Never mind. Resolved. Apparently the ORM can deal with joins.
To do it with a join (without an ORM) :
SELECT * FROM Rooms INNER JOIN RoomAttributes ON Rooms.id = RoomAttributes.room_id WHERE (RoomAttributes.resource = 'Projectors' or RoomAttributes.resource = 'Monitor') GROUP BY Room.id ORDER BY count(Room.id) desc
That should return all the rooms order by rooms with the most matching attributes first.
---------------------------
Question here. Old question solved and below.
In my subquery I'm returned an ordered list of IDs. How do I get the main query to return the resulted ordered by the subquery order? (WITHOUT doing a join)
SELECT * FROM Rooms WHERE id IN (SELECT room_id FROM RoomAttributes where (resource = 'Projectors' or resource = 'Monitor') group by room_id order by count(room_id) desc)
I want this to provide me with the rooms ordered by the most matching resources. The subquery will return the right order, but not when it get to the main query..
The main constraint is that I can only change after the WHERE clause. I can't change SELECT * FROM Rooms WHERE. I'm using an ORM and I'm exploring to see if I can keep the ORM functionality without doing a custom sql query.
----------- SOLVED ----------------------
In my subquery, I need to get all the records that match, group it by a column and then order the count for that column.
for example,
I have 2 tables. Rooms tables and RoomAttributes tables.
This is the sql I want (though it doesn't work). I would love to see how to make it work.
SELECT * FROM Rooms WHERE id IN (SELECT room_id, count(room_id) as count FROM RoomAttributes where (resource = 'Projectors' or resource = 'Monitor') group by room_id order by count desc)
Basically, I want the subquery to be ordered by a count AND provide the room_id to the main query. This would provide me with the rooms ordered by the most matching resources. Just providing the count isn't enough and getting the room_id isn't exactly want I need.
Solution :
SELECT * FROM Rooms WHERE id IN (SELECT room_id FROM RoomAttributes where (resource = 'Projectors' or resource = 'Monitor') group by room_id order by count(room_id) desc)
To do it with a join (without an ORM) :
SELECT * FROM Rooms INNER JOIN RoomAttributes ON Rooms.id = RoomAttributes.room_id WHERE (RoomAttributes.resource = 'Projectors' or RoomAttributes.resource = 'Monitor') GROUP BY Room.id ORDER BY count(Room.id) desc
That should return all the rooms order by rooms with the most matching attributes first.
---------------------------
Question here. Old question solved and below.
In my subquery I'm returned an ordered list of IDs. How do I get the main query to return the resulted ordered by the subquery order? (WITHOUT doing a join)
SELECT * FROM Rooms WHERE id IN (SELECT room_id FROM RoomAttributes where (resource = 'Projectors' or resource = 'Monitor') group by room_id order by count(room_id) desc)
I want this to provide me with the rooms ordered by the most matching resources. The subquery will return the right order, but not when it get to the main query..
The main constraint is that I can only change after the WHERE clause. I can't change SELECT * FROM Rooms WHERE. I'm using an ORM and I'm exploring to see if I can keep the ORM functionality without doing a custom sql query.
----------- SOLVED ----------------------
In my subquery, I need to get all the records that match, group it by a column and then order the count for that column.
for example,
I have 2 tables. Rooms tables and RoomAttributes tables.
This is the sql I want (though it doesn't work). I would love to see how to make it work.
SELECT * FROM Rooms WHERE id IN (SELECT room_id, count(room_id) as count FROM RoomAttributes where (resource = 'Projectors' or resource = 'Monitor') group by room_id order by count desc)
Basically, I want the subquery to be ordered by a count AND provide the room_id to the main query. This would provide me with the rooms ordered by the most matching resources. Just providing the count isn't enough and getting the room_id isn't exactly want I need.
Solution :
SELECT * FROM Rooms WHERE id IN (SELECT room_id FROM RoomAttributes where (resource = 'Projectors' or resource = 'Monitor') group by room_id order by count(room_id) desc)
Last edited:
