Different ways of building a query in Joomla?

Question

Just starting out, this should be a simple one but I haven't been able to find a good source, especially with the 1.5 to 1.6/1.7/2.5 changeover.

Building a component and among other issues keep running into syntax issues.

For example, here's one way I'm building 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
    ');

Confusing part about both of those is that mysql calls like "LEFTJOIN" are one word instead of two. So then if I want to use INSERT…ON DUPLICATE KEY UPDATE I'm totally lost.

Here's a third: [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 queries built but can't get to work [EDITED - working now, 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've gone through the Joomla MVC tutorial and Lynda...neither one really addresses this beyond "here's 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 these?

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

3) Is there a good source for learning different ways of building these? I've searched for hours and haven't found anything comprehensive.

Thanks!

Answer 1

1. Your type 1,2 & 3 are using the newer JDatabaseQuery object, whereas type 4 is using the older style to set the query to an SQL string.
2. If type 4 isn't working do you get an SQL error? (Have you turned on Joomla's debug mode so you can get the SQL error dumped out with the app profile?)
3. JDatabaseQuery is used to provide the abstraction required to support multiple SQL databases rather than just mySQL, thats why the $query object has functions like select(),where(),join(), etc...
4. Remember that the ->leftJoin() is a PHP call to the JDatabaseQuery function called leftJoin() and therefore must be one word ie. it can't be two.
5. leftJoin() actually just calls join('LEFT',$conditions) so it's a readability implementation more than a functional difference.
6. So, types 1,2 & 3 are effectively the same they're just have different degrees of readability (eg. IMHO type 2 & 3 are harder to read and maintain than type 1).

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

The only other source is Andrew Eddies "Learn the Art of Joomla!". I haven't used Andrew's stuff myself but I've heard lots of good things about it a the last Joomla! Day I attended.

Edit Based on the error I would say it's because you have the identifier u.id encased as one identifier. Try something like:

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