Re: Particular find operation to do

On Thu, Dec 20, 2012 at 5:21 AM, byqsri <marco.rizzetto@gmail.com> wrote:
> Yes the table is indexed correctly.
> The problem is in the length of the query.

Using IN() is very inefficient in MyQSL to begin with. It's not
surprising that passing thousands of IDs to it fails.

This query should get what you want.

SELECT id FROM users LEFT OUTER JOIN (SELECT user_id FROM groups_users
WHERE group_id = xx) AS tmp ON users.id = tmp.user_id WHERE
tmp.user_id IS NULL;

You can create an association using a finderQuery in your Group model:

public $hasMany = array(
'NonMember' => array(
'className' => 'User',
'foreignKey' => false,
'finderQuery' => 'SELECT User.id FROM users User
LEFT OUTER JOIN (
SELECT GroupUser.user_id FROM groups_users GroupUser
WHERE GroupUser.group_id = {$__cakeID__$}
) AS tmp
ON User.id = tmp.user_id
WHERE tmp.user_id IS NULL'
)
);

Then, when you fetch the data for a given Group, you should have a
NonMember array as well. This query will only grab the IDs of the
Users. If you want a list of names, etc. include those columns after
SELECT.

And if you want to run a query on Group but don't want all these
NonMembers just temporarily detach this model association.

http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#creating-and-destroying-associations-on-the-fly

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com.
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.