Subquery inside leftJoin

2 Posts

aloshka Reply #1, 10 years, 9 months ago

Hi all! Please, help me!

How I construct this query with xpdo?

SELECT  `msDiscountCard`.`id`,
		`msDiscountCard`.`uid`,
		`msDiscountCard`.`public`,
		`msDiscountCard`.`discount_id`,
		`msDiscountCard`.`amount`,
		`msDiscountCard`.`amount_used`
	FROM `modx_ms2_discount_card` AS `msDiscountCard`
	LEFT JOIN (
		SELECT  `msDiscountCardMember`.`discount_card_id`,
				`msDiscountCardMember`.`user_id`,
				`msDiscountCardMember`.`owner_type`
			FROM `modx_ms2_discount_card_members` AS `msDiscountCardMember`
			WHERE `msDiscountCardMember`.`owner_type` = 'owner'
	) AS `msDiscountCardMember`
	ON `msDiscountCardMember`.`discount_card_id` = `msDiscountCard`.`id`

Yes, I seen this article - http://web.archive.org/web/20120310104826/http://lazylegs.info/articles/subqueries-in-modx-revolution-using-xpdo/subqueries-in-modx-revolution-inside-join.html (original page returned 404, so link to the 'web archive' page), but it didn't help me :-(

I tried in this way:

$query = $modx->newQuery('msDiscountCard');
$subquery = $modx->newQuery('msDiscountCardMember');
$subquery->select('msDiscountCardMember.*');
$subquery->where(array(
	'msDiscountCardMember.owner_type' => 'owner'
));
$query->select('msDiscountCard.*, msDiscountCardMember.*');
$query->leftJoin('msDiscountCardMember', 'msDiscountCardMember', 'msDiscountCard.id = msDiscountCardMember.discount_card_id', $subquery);
$cards = $modx->getIterator('msDiscountCard', $query);

But I get only this incomplete query;

SELECT msDiscountCard.*,
		msDiscountCardMember.*
	FROM `modx_ms2_discount_card` AS `msDiscountCard`
	LEFT JOIN `modx_ms2_discount_card_members` `msDiscountCardMember`
	ON msDiscountCard.id = msDiscountCardMember.discount_card_id

[ed. note: aloshka last edited this post 10 years, 9 months ago.]

2 Posts

aloshka Reply #2, 10 years, 9 months ago

Done.

I change source query to:

SELECT  `msDiscountCard`.`id`,
		`msDiscountCard`.`uid`,
		`msDiscountCard`.`public`,
		`msDiscountCard`.`discount_id`,
		`msDiscountCard`.`amount`,
		`msDiscountCard`.`amount_used`,
		`msDiscountCardMember`.`user_id`,
		`msDiscountCardMember`.`owner_type`,
		`msDiscountCardMember`.`discount_card_id`
	FROM `modx_ms2_discount_card` AS `msDiscountCard`
	LEFT JOIN `modx_ms2_discount_card_members` AS `msDiscountCardMember`
	ON `msDiscountCardMember`.`discount_card_id` = (
		SELECT  `msDiscountCardMember`.`discount_card_id`
			FROM `modx_ms2_discount_card_members` AS `msDiscountCardMember`
			WHERE `msDiscountCardMember`.`owner_type` = 'owner'
				AND `msDiscountCard`.`id` = `msDiscountCardMember`.`discount_card_id`
			LIMIT 1
	)

And construct it with xPDO in this way:

$subquery = $modx->newQuery('msDiscountCardMember');
$subquery->select('`msDiscountCardMember`.`discount_card_id`');
$subquery->where(array(
	'`msDiscountCardMember`.`owner_type`' => 'owner',
	'`msDiscountCard`.`id` = `msDiscountCardMember`.`discount_card_id`'
));
$subquery->limit(1);
$query = $modx->newQuery('msDiscountCard');
$query->select('`msDiscountCard`.*, `msDiscountCardMember`.`user_id`');
if ($subquery->prepare()) {
	$query->leftJoin('msDiscountCardMember', 'msDiscountCardMember', array(
		'`msDiscountCardMember`.`discount_card_id` = ('. $subquery->toSQL() .')'
	));
}

Thank you, lazylegs!