SQL: Need someone else to look at this, please

[eluser]coding_hero[/eluser]
This is probably simple, but I'm not getting anywhere with it. I think I've been looking at it too long.

I have two tables, user_access and collection. The table user_access contains the following values:

user_id | collection_id
========================
4 | 3
7 | 3
3 | 15
4 | 15
7 | 15

collection contains the following values:

id | name
===========================
1 | Some entry
3 | This is Sample entry 3
15 | Yet another entry

What I'm trying to do is find all 'collections' that are NOT associated with a UID. So, for example, UID 4 has access to 3 and 15, but not 1. Here's the SQL I'm using:

Code:

SELECT DISTINCT collection.id, collection.name
FROM collection
WHERE collection.id NOT
IN (
SELECT collection_id
FROM user_access
WHERE user_access.user_id =4
)

And it works fine in phpMyAdmin. However, when I use it in code, it returns 3 results. Here's the contents of the model function:

Code:

public function get_collections_unavailable_to_user ($user_id)
{
     $query = array (); // wanted to make sure that this is cleared out.
     $this->db->select ( 'collection.id, collection.name' );
     $this->db->distinct ();
     $this->db->from ('collection');
     $this->db->where_not_in ( 'collection.id',
                            "( SELECT collection_id from user_access
                               WHERE user_access.user_id = ${user_id} )");
     $query = $this->db->get ();

     $dbg = $query->num_rows; // for $user_id = 4, $dbg is 3!?

     return ($query->result());
}

[eluser]pistolPete[/eluser]
A general advice: Use the profiler to see which queries were actually generated.

Code:

$this->output->enable_profiler(TRUE);

ActiveRecord does currently not support the use of round brackets. (see here, here, here, etc.)

I suggest using the plain SQL statement instead:

Code:

$this->db->query('SELECT DISTINCT collection.id, collection.name ...');

[eluser]coding_hero[/eluser]
[quote author="pistolPete" date="1235774552"]A general advice: Use the profiler to see which queries were actually generated.

Code:

$this->output->enable_profiler(TRUE);

[/quote]

Aha! Thanks, I was looking for something like that. I eventually started stepping into the ActiveRecords class to see what SQL it was generating mere minutes before you posted.

I suggest using the plain SQL statement instead:

Code:

$this->db->query('SELECT DISTINCT collection.id, collection.name ...');

Yah, that's the conclusion I came to also. Glad to know it wasn't me doing something stupid. I can blame the class for this one

Thanks for the help!