I reckon Joomla's query method select() has confused you. This is a good reason why I don't like to make multiple select() calls -- because only ONE SELECT clause is actually generated.
If you call echo $query->dump(); immediately after building your query, you will see the following query string (with your db prefix in place of #_):
SELECT a.id as id, a.greeting as greeting, a.published as published,`c`.`title` AS `category_title`
FROM `lmnop_diolegend` AS `a`
LEFT JOIN `lmnop_categories` AS `c` ON c.id =a.catid
I mean, you could achieve the same generated query by calling select() on each column name.
->select('a.id AS id')
->select('a.greeting AS greeting')
->select('a.published AS published')
->select('c.title AS category_title')
Of course, it is perfectly valid to join two database tables together using columns that you have no intention of returning in the result set.
By joining the two tables, you have the ability to nominate one or all of the columns from both of the tables involved in the query. In fact, it is best practice to NOT include columns unless you are going to use them in your script. Using * is the simple/brief way to select all of the columns in a queried table, but it doesn't make sense to ask the database to return data that you don't need.
Sometimes you might see an error that complains about a column not being in the SELECT clause when you use GROUP BY, but JOINs won't make this complaint.
Some notes about your query building code:
- By "chaining" the methods to the initial
$query declaration, you can avoid typing $query over and over for each new method call.
- When using aliases on your columns in the SELECT clause, there is no point in declaring an alias if it is the same as your column name. *The table aliases (
a. and c.) are not used when accessing the elements in the result set.)
- To avoid any confusion between the query building code and the generated query, I prefer to use
select() only once and feed it a single string of columns so that all of the columns are in one place in the code.
- Because none of your column or table names contain any characters that will cause an issue/error with SQL, you can omit the
quoteName() calls -- not that it does any harm to keep them in, it's just that they are unnecessary.
- The
AS keyword is not necessary in my snippet below, but I believe it improves readability.
- As a matter of offering simple/helpful debugging features to my answer, I'll include a
try{}catch{} block, dump of the rendered query, and a check for no returned rows.
Here is a snippet that I tested to be successful on my localhost:
$db = JFactory::getDbo();
try {
$query = $db->getQuery(true)
->select('a.id, a.greeting, a.published, c.title AS category_title')
->from("#__diolegend AS a")
->leftJoin("#__categories AS c ON c.id = a.catid");
echo $query->dump();
$db->setQuery($query);
$resultset = $db->loadAssocList();
if (!$resultset) {
echo "No Rows In Resultset";
} else {
foreach ($resultset as $row) {
echo "<div>";
echo "<div>ID: {$row['id']}</div>";
echo "<div>Greeting: {$row['greeting']}</div>";
echo "<div>Published: {$row['published']}</div>";
echo "<div>Category Title: {$row['category_title']}</div>";
echo "</div>";
}
}
} catch (Exception $e) {
echo "Syntax Error " , $e->getMessage(); // never show `$e->getMessage()` on a public site (only use privately / before your site is live)
}
This will output:
SELECT a.id, a.greeting, a.published, c.title AS category_title
FROM lmnop_diolegend AS a
LEFT JOIN lmnop_categories AS c ON c.id = a.catid
then a result set like:
ID: 1
Greeting: Hello
Published: 1
Category Title: English
ID: 2
Greeting: Bonjour
Published: 1
Category Title: French
