I’m currently writing a solution for users to create combinations of items, my database schema looks like this :
tbl_combination (id, …)
tbl_combination_item (id, combination_id, font_id)
tbl_item (id, …)
I created 2 models, Combination and Item, configured with MANY_MANY relations, and I use the activerecord-relation extension to easily save the new combinations. Everything works fine except that I’d like to verify that a combination doesn’t already exists before saving it.
Basically this means that there shouldn’t exist any combination with the same items and the same number of items.
Let’s say the user is trying to create a new combination with the items id 4 and 6.
The SQL to check if this combination already exists would be like
SELECT combination_id FROM tbl_combination_item WHERE item_id IN (4,6) GROUP BY combination_id HAVING COUNT(id) = 2;
What’s the “Yii way” to do this ? I have created a new validation rule with the raw SQL, but I’d like to know if this could be done via the built-in “unique” rule, or maybe if the SQL can be built using some helpers ?
I’ve searched the forum, doc and wiki, but I haven’t found this explained. I’m pretty new to Yii.