Joomfish is a very powerful tool that supports many types of MySQL queries including multi-table queries and fieldname & table aliases.   This article summarises how to construct queries to enable Joomfish support.

There are basically a few golden rules to follow:

1. You must include the primary key from every table you want translated in the query

Single Table Example 

Incorrect :  "SELECT name, info FROM #__table1"

Correct : "SELECT id1, name, info FROM #__table1  or "SELECT * FROM #__table1"

Multi Table Example

Incorrect :  "SELECT name, info, tab2field FROM #__table1 AS t1 LEFT JOIN #__table2 AS t2 ON t1.id1=t2.fkey"

Correct : "SELECT id1, name, info, id2, tab2field FROM #__table1 AS t1 LEFT JOIN #__table2 AS t2 ON t1.id1=t2.fkey" (Note that id1 is the primary key of table1 and id2 is the primary keyof table2)

2. Where the primary key has the same name in more than one table you must add a specific alias for each primary key from every table

In this example the primary key is "id" for both #__table1 and #__table2.

Incorrect :  "SELECT t1.*, t2.*  FROM #__table1 AS t1 LEFT JOIN #__table2 AS t2 ON t1.id=t2.fkey"

Correct : "SELECT t1.id AS key1, t1.*, t2.id AS key2, t2* FROM #__table1 AS t1 LEFT JOIN #__table2 AS t2 ON t1.id=t2.fkey"

3. When using aliases you must include the "AS"

Incorrect : "SELECT t1.id AS key1, t1.*, t2.id AS key2, t2* FROM #__table1 t1 LEFT JOIN #__table2 t2 ON t1.id=t2.fkey"

Correct : "SELECT t1.id AS key1, t1.*, t2.id AS key2, t2* FROM #__table1 AS t1 LEFT JOIN #__table2 AS t2 ON t1.id=t2.fkey"

Unsupported Query Types

This is a list of query types that are not supported by Joomfish:

•  INSERT ... SELECT (see mysql documentation)