How to write e.g. left join with nested select stetement? Topic is solved

For Joomla! 4.x Coding related discussions, you could also use: http://groups.google.com/group/joomla-dev-general

Moderators: ooffick, General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10.
Post Reply
JanoD
Joomla! Apprentice
Joomla! Apprentice
Posts: 15
Joined: Tue May 07, 2024 5:12 pm

How to write e.g. left join with nested select stetement?

Post by JanoD » Mon Jun 10, 2024 10:36 am

Hello,

is there a preferred way for writing nested select or statements or e.g. a select statement nested in a join statement? In e.g.: https://manual.joomla.org/docs/general- ... elect-data is see no examples for such statements.

Any example would be appreciated.

Thanks!

User avatar
brian
Joomla! Master
Joomla! Master
Posts: 12811
Joined: Fri Aug 12, 2005 7:19 am
Location: Leeds, UK
Contact:

Re: How to write e.g. left join with nested select stetement?

Post by brian » Mon Jun 10, 2024 11:17 am

"Exploited yesterday... Hacked tomorrow"
Blog http://brian.teeman.net/
Joomla Hidden Secrets http://hiddenjoomlasecrets.com/

SharkyKZ
Joomla! Hero
Joomla! Hero
Posts: 2993
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

Re: How to write e.g. left join with nested select stetement?

Post by SharkyKZ » Mon Jun 10, 2024 11:19 am

Can you post an example of raw SQL you want to achieve?

JanoD
Joomla! Apprentice
Joomla! Apprentice
Posts: 15
Joined: Tue May 07, 2024 5:12 pm

Re: How to write e.g. left join with nested select stetement?

Post by JanoD » Mon Jun 10, 2024 5:00 pm

This is the query I am trying to re-write in J4. There is a nested select statement with left join in an outer left join.

Code: Select all

$query = 
" SELECT u.id, u.name AS user_name, t.concatenated_topics as topics
  FROM #__users AS u 
  LEFT JOIN (SELECT id_user, GROUP_CONCAT(t.name) as concatenated_topics 
                   FROM #__user_topics
                   LEFT JOIN #__topics as t on t.id = id_topic 
                   GROUP BY id_user) AS t on t.id_user = u.id

SharkyKZ
Joomla! Hero
Joomla! Hero
Posts: 2993
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

Re: How to write e.g. left join with nested select stetement?

Post by SharkyKZ » Mon Jun 10, 2024 7:57 pm

Query objects are stringable. You can build the inner query and insert it into the outer query as a string:

Code: Select all

$innerQuery->select('id_user, GROUP_CONCAT(t.name) AS concatenated_topics')
    ->from('#__user_topics')
    ->join('LEFT', '#__topics AS t', 't.id = id_topic')
    ->group('id_user');
$outerQuery->select('u.id, u.name AS user_name, t.concatenated_topics AS topics')
    ->from('#__users AS u')
    ->join('LEFT', '(' . $innerQuery . ') AS t', 't.id_user = u.id');
Note, if you have any parameters in the inner query, you have to bind them to the outer query.

JanoD
Joomla! Apprentice
Joomla! Apprentice
Posts: 15
Joined: Tue May 07, 2024 5:12 pm

Re: How to write e.g. left join with nested select stetement?

Post by JanoD » Tue Jun 11, 2024 5:56 am

Thanks, this is what I was looking for!

I noticed that I also had to do

Code: Select all

$innerQuery = $db->getQuery(true);
before building the inner query.

SharkyKZ
Joomla! Hero
Joomla! Hero
Posts: 2993
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

Re: How to write e.g. left join with nested select stetement?

Post by SharkyKZ » Tue Jun 11, 2024 6:04 am

Yes, you need to create a new instance for each query.


Post Reply

Return to “Joomla! 4.x Coding”