Complex Queries

General questions relating to Joomla! 3.x.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting.
Forum Post Assistant - If you are serious about wanting help, you should use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10
Post Reply
thommango
Joomla! Apprentice
Joomla! Apprentice
Posts: 9
Joined: Mon Dec 07, 2020 1:15 am

Complex Queries

Post by thommango » Tue Oct 19, 2021 4:02 pm

I've got a complex query that works for me in PHPMyAdmin, and now I'd like to include it in a php file. I'm wondering if there are examples, tools or workarounds that might help with a query such as this one?

Code: Select all

SELECT slips.id as slip, rate_plans.rate_plan_name, slips.bookable, slips.full_slip_name as slip_name, seasons.per_foot_cost, seasons.start_month, 
seasons.per_foot_weekday_rate, seasons.per_beam_foot_weekday_rate, seasons.flat_fee_weekday_rate, 
seasons.reciprocal_club_discount, seasons.reciprocal_duration, seasons.flat_fee_weekend_rate, 
seasons.per_beam_foot_weekend_rate, seasons.first_discount_rate, seasons.first_discount_threshold_nights_, 
seasons.second_discount_rate, seasons.second_discount_threshold, seasons.per_beam_foot_cost, 
seasons.flat_fee_per_night, guest_bookings.stay_date
FROM `slips` 
LEFT JOIN `guest_bookings` 
ON slips.id=guest_bookings.assigned_slip 
CROSS JOIN rate_plans
ON slips.rate_plan = rate_plans.id
CROSS JOIN seasons
ON rate_plans.id = seasons.rate_plan_id
 CROSS JOIN (SELECT Max(seasons.start_month) AS MaxStartMonth, seasons.rate_plan_id
            FROM seasons
            WHERE seasons.start_month < 9
           	GROUP BY seasons.rate_plan_id) AS blurb
ON seasons.rate_plan_id = blurb.rate_plan_id
AND seasons.start_month = blurb.MaxStartMonth
WHERE (NOT stay_date BETWEEN '2021-09-01' AND '2021-09-03' 
AND seasons.start_month < 9
AND slips.bookable = 1)
OR stay_date is NULL
AND seasons.start_month < 9
AND slips.bookable = 1

thommango
Joomla! Apprentice
Joomla! Apprentice
Posts: 9
Joined: Mon Dec 07, 2020 1:15 am

Re: Complex Queries

Post by thommango » Tue Oct 19, 2021 6:26 pm

Here's what I came up with so far. It spits out a general 1094, "error in your sql statement" message:

Code: Select all

	$db = JFactory::getDbo();					//get a list of slips that have bookings with same date
	
	$subquery = $db
		->getQuery(true)
		->select('Max('. $db->quoteName('seasons.start_month') . ')AS MaxStartMonth,' . $db->quoteName('seasons.rate_plan_id'))
		->from($db->quoteName('seasons'))
		->where($db->quoteName('seasons.start_month') . '< 9')
		->group($db->quoteName('seasons.rate_plan_id'));
		
	
	$query = $db
	->getQuery(true)
 	->select($db->quoteName('slips.id, rate_plans.rate_plan_name, slips.bookable, slips.full_slip_name, seasons.per_foot_cost, seasons.start_month, 
		seasons.per_foot_weekday_rate, seasons.per_beam_foot_weekday_rate, seasons.flat_fee_weekday_rate, 
		seasons.reciprocal_club_discount, seasons.reciprocal_duration, seasons.flat_fee_weekend_rate, 
		seasons.per_beam_foot_weekend_rate, seasons.first_discount_rate, seasons.first_discount_threshold_nights_, 
		seasons.second_discount_rate, seasons.second_discount_threshold, seasons.per_beam_foot_cost, 
		seasons.flat_fee_per_night, guest_bookings.stay_date'))
		->join('LEFT', $db->quoteName('guest_bookings') . ' ON ' . $db->quoteName('slips.id') . ' = ' . $db->quoteName('guest_bookings.assigned_slip'))
		->join('CROSS', $db->quoteName('rate_plans') . ' ON ' . $db->quoteName('slips.rate_plan') . ' = ' . $db->quoteName('rate_plans.id'))
		->join('CROSS', $db->quoteName('seasons') . ' ON ' . $db->quoteName('rate_plans.id') . ' = ' . $db->quoteName('seasons.rate_plan_id'))
		->join('CROSS', $subquery . ' AS sub_tree ON ' . $db->quoteName('seasons.rate_plan_id') . ' = ' . $db->quoteName('sub_tree.rate_plan_id') . ' AND ' . 
		$db->quoteName('seasons.start_month') . ' = ' . $db->quoteName('sub_tree.MaxStartMonth')) 
		->where('(NOT '. $db->quoteName('stay_date') . ' BETWEEN  "' . $startDate->format('Y-m-d') . '" AND "' . $endDate->format('Y-m-d') . '" AND ' . $db->quoteName('seasons.start_month') . '< 9' . 
		' AND ' . $db->quoteName('slips.bookable') . ' = 1)' . 
		' OR '	. $db->quoteName('stay_date') . ' IS NULL AND ' . $db->quoteName('seasons.start_month') . ' < 9 AND ' . $db->quoteName('slips.bookable') . 
		' = 1');
		

 	$db->setQuery($query);
 	$availableSlips = $db->loadRowList();
		
	echo print_r($availableSlips);

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

Re: Complex Queries

Post by SharkyKZ » Tue Oct 19, 2021 7:13 pm

If you pass multiple columns to $db->quoteName(), you have to pass them as array:

Code: Select all

$query->select(
	$db->quoteName(
		[
			'slips.id',
			'rate_plans.rate_plan_name',
			...
		]
	)
)
You can cast the query to string or call $query->dump() to see the resulting SQL to help with debugging.

thommango
Joomla! Apprentice
Joomla! Apprentice
Posts: 9
Joined: Mon Dec 07, 2020 1:15 am

Re: Complex Queries

Post by thommango » Wed Oct 20, 2021 12:35 am

Thanks Sharky. I took your advice for the array, but I can't seem to $query->dump() work. I've tried it in an echo statement and as this statement:
JFactory::getApplication()->enqueueMessage($query->dump());
But I still see only the error message and not the query text.

Here's what my query looks like now.

Code: Select all

	$db = JFactory::getDbo();					//get a list of slips that have bookings with same date
	
	$subquery = $db
		->getQuery(true)
		->select('Max('. $db->quoteName('seasons.start_month') . ')AS MaxStartMonth,' . $db->quoteName('seasons.rate_plan_id'))
		->from($db->quoteName('seasons'))
		->where($db->quoteName('seasons.start_month') . '< 9')
		->group($db->quoteName('seasons.rate_plan_id'));
		
	$query = $db
		->getQuery(true)
 		->select($db->quoteName['
 			slips.id, 
 			rate_plans.rate_plan_name, 
 			slips.bookable, 
 			slips.full_slip_name, 
 			seasons.per_foot_cost, 
 			seasons.start_month, 
			seasons.per_foot_weekday_rate,
			seasons.per_beam_foot_weekday_rate, 
			seasons.flat_fee_weekday_rate, 
			seasons.reciprocal_club_discount, 
			seasons.reciprocal_duration, 
			seasons.flat_fee_weekend_rate, 
			seasons.per_beam_foot_weekend_rate, 
			seasons.first_discount_rate, 
			seasons.first_discount_threshold_nights_, 
			seasons.second_discount_rate, 
			seasons.second_discount_threshold, 
			seasons.per_beam_foot_cost, 
			seasons.flat_fee_per_night, 
			guest_bookings.stay_date'])
		
		->join('LEFT', $db->quoteName('guest_bookings') . ' ON ' . $db->quoteName('slips.id') . ' = ' . 
			$db->quoteName('guest_bookings.assigned_slip'))
			
		->join('CROSS', $db->quoteName('rate_plans') . ' ON ' . $db->quoteName('slips.rate_plan') . ' = ' . 
			$db->quoteName('rate_plans.id'))
			
		->join('CROSS', $db->quoteName('seasons') . ' ON ' . $db->quoteName('rate_plans.id') . ' = ' . 
			$db->quoteName('seasons.rate_plan_id'))
			
		->join('CROSS', $subquery . ' ON ' . $db->quoteName('seasons.rate_plan_id') . ' = ' . 
			$db->quoteName('srate_plan_id') . 'IN ()'. $subquery .') AND ' . 
			$db->quoteName('seasons.start_month') . ' = ' . $db->quoteName('MaxStartMonth') . 'IN ('. $subquery . ')')
			
		->where('(NOT '. $db->quoteName('stay_date') . ' BETWEEN  "' . $db->quote($startDate->format('Y-m-d')). '" AND "' . 
			$db->quote($endDate->format('Y-m-d')) . '" AND ' . 
			$db->quoteName('seasons.start_month') . ' < '. $db->quote('9') . 
			' AND ' . $db->quoteName('slips.bookable') . ' = ' . $db->quote('1') . 
			' OR '	. $db->quoteName('stay_date') . ' IS NULL AND ' . 
			$db->quoteName('seasons.start_month') . ' < '. $db->quote('9') .  ' AND ' . 
			$db->quoteName('slips.bookable') . 
			' = ' . $db->quote('1'));

	
	
 	$db->setQuery($query);
 	$availableSlips = $db->loadObjectList();

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

Re: Complex Queries

Post by SharkyKZ » Wed Oct 20, 2021 5:18 am

It's because the code is still being executed. You can either exit the script after outputting the query or catch the thrown exception and output the query there.

Also the change you made is invalid. You're now trying to use non-existing quoteName property as array.

thommango
Joomla! Apprentice
Joomla! Apprentice
Posts: 9
Joined: Mon Dec 07, 2020 1:15 am

Re: Complex Queries

Post by thommango » Wed Oct 20, 2021 2:02 pm

Thanks. This has helped very much. Now that I can see the query, I notice (among other issues) that the < symbol doesn't appear in the query dump. I tried putting it inside a $db->quote but that doesn't help. The field in question is an integer field. Any thoughts on why the < is disappearing from the query?

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

Re: Complex Queries

Post by SharkyKZ » Wed Oct 20, 2021 2:16 pm

I can't reproduce this issue. If you're viewing this in a browser, try using htmlspecialchars() to escape HTML characters.


Post Reply

Return to “General Questions/New to Joomla! 3.x”