[eluser]Unknown[/eluser]
Hi there,
i am pretty new to DMZ, however I have some problems I was not able to solve by reading the (good) documentation:
1. creating more complex queries with count:
example: I have a users table and a user_group table(each user belongs to 1 group). I want to show a list of all groups and the appropriate number of users belonging to this group.
I want to use only 1 query (performance reasons: I have thousands of groups)
in SQL this is simple:
SELECT ug.* , count( u.id ) as count
FROM user_groups` ug
LEFT JOIN users u ON ug.id = u.group_id
WHERE mg.user_id = ?
GROUP BY mg.id
How can I do this in DMZ?
2. Delete relation, avoid setting id to NULL
example:
User table and media table with 1:n relation (1 user has many media) by user.id = media.user_id
If I delete a user(eg id=2), all media.user_id will be set to NULL which were 2 before. In SQL:
UPDATE `media` SET `user_id` = NULL WHERE `user_id` = '2'
However I want to completely remove them like this:
DELETE FROM media WHERE `user_id` = '2'
3. Unnecessary SELECT statements:
Before the Query from 2 there is always a
