Different ways to build a query in Joomla?

Question

Just get started, it should be simple, but I could not find a good source, especially when switching from 1.5 to 1.6 / 1.7 / 2.5.

Building a component, and among other problems, continues to run into syntax problems.

For example, here is one way to create a query: [TYPE 1]

$query->SELECT('u.id as UserID , u.name AS Name , uppi.profile_value AS Hair , uppi2.profile_value AS Height '); $query->FROM (' #__users AS u'); $query->LEFTJOIN (' #__user_profiles AS uppi ON u.id = uppi.user_id AND uppi.ordering = 1 '); $query->LEFTJOIN (' #__user_profiles AS uppi2 ON u.id = uppi2.user_id AND uppi2.ordering = 2 '); $query->GROUPBY (' u.id , u.name , uppi.profile_value '); 

Another way: [TYPE 2]

 $query->SELECT('u.id as UserID , u.name AS Name , uppi.profile_value AS Hair , uppi2.profile_value AS Height ') ->FROM (' #__users AS u') ->LEFTJOIN (' #__user_profiles AS uppi ON u.id = uppi.user_id AND uppi.ordering = 1 ') ->LEFTJOIN (' #__user_profiles AS uppi2 ON u.id = uppi2.user_id AND uppi2.ordering = 2 ') ->GROUPBY (' u.id , u.name , uppi.profile_value '); 

The confusing part to both is that mysql calls like LEFTJOIN are one word instead of two. So, if I want to use INSERT…ON DUPLICATE KEY UPDATE , I get completely lost.

Here's the third one: [TYPE 3]

 $query->SELECT('u.id as UserID , u.name AS Name , uppi.profile_value AS Hair , uppi2.profile_value AS Height '); $query->FROM (' #__users AS u'); $query->JOIN ('LEFT', ' #__user_profiles AS uppi ON u.id = uppi.user_id AND uppi.ordering = 1 '); $query->JOIN ('LEFT', ' #__user_profiles AS uppi2 ON u.id = uppi2.user_id AND uppi2.ordering = 2 '); $query->GROUPBY (' u.id , u.name , uppi.profile_value '); $query->ORDER ('u.name ASC'); 

This is another way I've seen in queries, but I can’t work [ EDITED - now I work, thanks @cppl!] [Type 4]

 $query= "SELECT `u`.`id` as UserID , `u`.`name` AS Name , `uppi`.`profile_value` AS Hair , `uppi2`.`profile_value` AS Height FROM `#__users` AS u LEFT JOIN `#__user_profiles` AS uppi ON `u`.`id` = `uppi`.`user_id` AND `uppi`.`ordering` = 1 LEFT JOIN `#__user_profiles` AS uppi2 ON `u`.`id` = `uppi2`.`user_id` AND `uppi2`.`ordering` = 2 "; 

I looked at the Joomla MVC and Lynda tutorial ... none of them affect this above "here is some code - PLOP".

 References: --//www.theartofjoomla.com/home/9-developer/135-database-upgrades-in-joomla-16.html --//stackoverflow.com/questions/8467858/joomla-1-7-db-query-does-not-work-when-query-has-an-ampersand --developer.joomla.org/standards/ch03s10.html --forum.joomla.org/viewtopic.php?p=2506722 --forum.joomla.org/viewtopic.php?p=1200668 --docs.joomla.org/API16:JDatabaseQuery --//fsadventures.com/2011/01/some-basic-joomla-database-call-functions/ --//www.sourcecodester.com/php/3863/updating-multiple-rows-mysql-using-php.html --//stackoverflow.com/questions/7047471/mysql-query-syntax-error 

Questions:

1) Are there any differences between the two?

2) Is there a reason to use one method over another?

3) Is there a good source for exploring various ways to build them? I looked for a watch and did not find anything comprehensive.

Thanks!

Answer 1

• Your types 1,2 and 3 use the new JDatabaseQuery object, while type 4 uses the old style to set the query to an SQL string.
• If type 4 does not work, do you get an SQL error? (Have you enabled Joomla debugging mode so that you can get the SQL error unloaded with the application profile?)
• JDatabaseQuery is used to provide the abstraction needed to support multiple SQL databases, not just mySQL, so the $query object has functions like select() , where() , join() , etc.
• Remember that ->leftJoin() is a PHP call to the JDatabaseQuery function called leftJoin() and therefore there must be one word ie. it cannot be two.
• leftJoin() actually just calls join('LEFT',$conditions) , so the read implementation is more than the functional difference.
• So, types 1,2 and 3 are practically the same, they just have different degrees of readability (for example, IMHO type 2 and 3 are more difficult to read and maintain than type 1).

You seem to have found all the online sources for Joomla! documentation other than Joomla! Google Groups ( CMS Dev , General Dev, and Platform Dev ).

The only other source is Andrew Eddies " Learn Joomla Art! " I myself did not use Andrew material, but I heard a lot of good things about him, about the latest Joomla! The day I attended.

Edit Based on the error, I would say because you have the identifier u.id wrapped as a single identifier. Try something like:

 SELECT `u`.`id` AS "UserID" FROM `#__users` AS `u`