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
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
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