Stored Procedure

Hello all,

I have read some tutorials on MYSQL stored procedures and find it very difficult to get my head around it. My first query is:

SELECT aChild.id, aChild.type, GROUP_CONCAT(aClient.client_id) AS `client_ids`, count(aClient.client_id) AS `count`
			FROM Asset_Child AS aChild
			JOIN Asset_Client AS aClient ON
				(
					aChild.`type` = aClient.attachment_type
					AND aClient.attachment_id = aChild.id
					AND aClient.enabled = 1
					AND aChild.`status` = 1
				)
			WHERE aClient.client_id IN (1,2)
			GROUP BY aChild.id, aChild.type
			HAVING count = 2
			ORDER BY aChild.created DESC
			LIMIT 0, 15

This gives me an array similar to this:
[id: 5, type: ‘image’], [id: 6, type: ‘text’], [id: 7, type: ‘link’], [id: 8, type: ‘image’], [id: 9, type: ‘text’], [id: 11, type: ‘link’]

I then loop through this and load the individual rows like this:


SELECT * FROM Asset_Image WHERE id = 5;
SELECT * FROM Asset_Text WHERE id = 6;
SELECT * FROM Asset_Link WHERE id = 7;

I would like to cut down the number of queries being issued and clean up the code, how would I use a stored procedure for this?

BTW I’m very aware it sounds like Im begging for an answer, thats not the case, I just need a push in the right direction :slight_smile:

you wouldn’t :slight_smile:

you can eliminate those extra queries by simply joining those tables to your original query

three left joins? You cant do if statements in a query? Besides the attachment_type is marked as ‘image’, ‘link’ and ‘text’, the tables are called Asset_Text, Asset_Link and Asset_Image

yes, left outer joins to implement your “if” capability, because the asset is going to match only one of those tables, right?

the tables it doesn’t match will have nulls returned in those columns, so your php code would display only the non-null stuff

Thanks, Ive made this query:

  	SELECT 
			aChild.`id`, aChild.`type`, GROUP_CONCAT(aClient.`client_id`) AS `client_ids`, count(aClient.`client_id`) AS `count`,
			assetImage.text, assetImage.label, assetImage.source,
			assetText.text, assetText.label,
			assetLink.text, assetLink.label
		FROM Asset_Child AS aChild 
		JOIN Asset_Client AS aClient ON 
			(
				aChild.`type` = aClient.`attachment_type` 
				AND aClient.`attachment_id` = aChild.`id` 
				AND aClient.`enabled` = 1 
				AND aChild.`status` = '.$status.'
			)
		LEFT JOIN Asset_Text AS assetText ON assetText.id = aChild.id 
		LEFT JOIN Asset_Link AS assetLink ON assetLink.id = aChild.id 
		LEFT JOIN Asset_Image AS assetImage ON assetImage.id = aChild.id 
		WHERE aClient.`client_id` IN ('.implode(',', $clientIds).')
		GROUP BY aChild.`id`, aChild.`type`
		HAVING count = ' . count($clientIds).'
		ORDER BY aChild.`created` DESC
		LIMIT '.$from.', '.$to

Im not sure this is optimised correctly?

start with something more important – does it work correctly?

It does

okay, next question, does it run fast?

3.0ms for 11 results

seems optimized to me… how about you?

Yeah I would say so, thank you!