2

I am using Joomla's JDatabaseQuery methods to select some columns from two joined tables, but I don't understand something about the following snippet.

//Initialize variables.
$db = JFactory::getDbo();
$query = $db->getQuery(true);

//Create the base select statement.
$query->select('a.id as id, a.greeting as greeting, a.published as published')
      ->from($db->quoteName('#__diolegend','a'));

//Join over the categories.
$query->select($db->quoteName('c.title' ,'category_title'))
      ->join('LEFT',$db->quoteName('#__categories','c') . ' ON c.id =a.catid');

See in the 2nd select(), there is no mention of a.catid?

How can it be possible to retrieve a.catid? We haven't selected it from anywhere.

  • What the entire query is.. – William Zhao Sep 27 '18 at 15:55
1

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
1

It's simple the $query object used to select data from table #__deolegend and then on the same $query object again a select() is called, which will add these to the fields that has to be selected, then adding a join on the #__categories table on c.id and a.catid, which means the #__deolegend must contain a column catid, it doesn't matter that you have to select the column to apply join. Also I will suggest that you print the $query object using :

echo $query;

So you can have a better idea of the final query. Also first check the structure of the tables. If you can share your findings on these here we may be able to help you better. Hope this helps

  • $query is an object here, you can't just echo it. – FFrewin Sep 27 '18 at 22:23
  • 1
    try it @FFrewin as it has tostring method for this purpose, the complete final query will be printed – Pratyush Sep 28 '18 at 4:58
  • you are right - I never ever noticed $query had some magic in. Thanks. – FFrewin Sep 28 '18 at 6:14
  • Welcome, we are here to explore new things aren't we – Pratyush Sep 28 '18 at 12:34
0

I am not sure what is your actual concern, ... but now after looking a bit closer to that Query, I am also not that sure what this Query is about.

I mean that...

The above query wanted to be a SELECT with a JOIN - but the JOIN occurs on the same table: #__diolegend and this really doesn't make any sense.

Is it a copy/paste/typo error, or where did you get this query from?


Hypothetically, the second table should be something like #__diolegend_categories or just #__categories and in this case, what that Query would have attempted to do is to get the id, greeting and published data from #__diolegend table with the related category title from the #__categories table -
Joined on the category id that exists in both tables, as a primary key in the #__categories table and as a foreign key in the #__diolegend.

  • +1 Ah yes I have changed the #__diolegend to #__categories, thanks, how sql stamens would be in this case? – William Zhao Sep 26 '18 at 17:56
  • select a.id as id, a.greeting as greeting, a.published as published from #__diolegend as a select c.title as category_title left join #__categories as c on c.id = a.catid ????? – William Zhao Sep 26 '18 at 17:59
  • @WilliamZhao - do you ask how the actual MySQL query would look like? With a quick look what you wrote above seems correct. You can use echo $query->dump(); and it will print the actual query. – FFrewin Sep 26 '18 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.