raw complex query

[wtricks]

I need to run this query:

SELECT * FROM `tblticketreplies` where tid IN (SELECT id FROM `tbltickets`  WHERE userid = '0' and date > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AND id in (SELECT max(id) FROM `tblticketreplies` GROUP BY tid ) and DATE(date) > DATE(DATE_SUB(NOW(), INTERVAL 7 DAY)) order by id desc

but I'm having problems, since I can't seem to make it work using raw (not really experienced with laravel query builder.

I tried with

$replies = Capsule::raw(SELECT * FROM `tblticketreplies` where tid IN (SELECT id FROM `tbltickets`  WHERE userid = '0' and date > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AND id in (SELECT max(id) FROM `tblticketreplies` GROUP BY tid ) and DATE(date) > DATE(DATE_SUB(NOW(), INTERVAL 7 DAY)) order by id desc);

But no luck. do you know how to do this?

[brian!]

for something like that, you shouldn't need to use RAW (though you could if you wanted to)...

$replies = Capsule::select("SELECT * FROM `tblticketreplies` where tid IN (SELECT id FROM `tbltickets`  WHERE userid = '0' and date > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AND id in (SELECT max(id) FROM `tblticketreplies` GROUP BY tid ) and DATE(date) > DATE(DATE_SUB(NOW(), INTERVAL 7 DAY)) order by id desc");

untested, but the above query should work and return an array of results - assuming there are any results that match the SQL query.

[wtricks]

thanks brian, it worked perfectly.

for the records I tried (only difference is raw instead of select):

$replies = Capsule::raw("SELECT * FROM `tblticketreplies` where tid IN (SELECT id FROM `tbltickets`  WHERE userid = '0' and date > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AND id in (SELECT max(id) FROM `tblticketreplies` GROUP BY tid ) and DATE(date) > DATE(DATE_SUB(NOW(), INTERVAL 7 DAY)) order by id desc");

and it doesn't work (I don't need it, this is ok already

[brian!]

when I said you could use raw, I meant that you could do....

$replies = Capsule::select(Capsule::raw("SELECT * FROM `tblticketreplies` where tid IN (SELECT id FROM `tbltickets`  WHERE userid = '0' and date > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AND id in (SELECT max(id) FROM `tblticketreplies` GROUP BY tid ) and DATE(date) > DATE(DATE_SUB(NOW(), INTERVAL 7 DAY)) order by id desc"));

but that capsule::raw is not necessary...