Welcome Guest, Not a member yet? Register   Sign In
Quick question regarding where_in/where_not_in Active Record
#1

[eluser]xzela[/eluser]
I've checkout the documentation for both CodeIgniter and MySQL regarding the where_in/where_not_in clause and I'm having trouble using it within the Active Record Class.

The MySQL documents state that sub queries can be ran within a WHERE IN/NOT IN clause, such as this:
Code:
SELECT * FROM table WHERE table.id NOT IN (SELECT other.id FROM other);
More can be found here: http://dev.mysql.com/doc/refman/5.0/en/a...eries.html

However if one uses that same query with active record:
Code:
$this->db->from('table');
$this->db->where_not_in('table.id', 'SELECT other.id FROM other');

it will produce this:
Code:
SELECT *
FROM ('table')
WHERE 'table'.'id' NOT IN ('SELECT other.id FROM other')

Those quotes around the NOT IN('SELECT ...') prevent the sub query from running. Is it possible to remove those single quotes without modifying the core too much? or should i just hand write the query?

Has anyone else ever encountered this?

Let me know what you guys think.
#2

[eluser]Armchair Samurai[/eluser]
Unlike where(), you can't add FALSE as a third parameter to prevent the escaping of variables (unless it's an undocumented feature - I haven't checked the source) with where_not_in(), so best to write out the subquery if you use AR.
Code:
$this->db->where('table.id NOT IN', '(SELECT other.id FROM other)', FALSE);
#3

[eluser]xzela[/eluser]
You know, I was thinking of modifying the core to accept a third parameter (to add escaping variables to where_not_in). But after trying to suggestion it actually worked! That was much easier than digging deep into the CI core.

Thanks for the trick!
#4

[eluser]Darwin Survivor[/eluser]
Sorry to bump a 2 1/2 moth old post, but I have a similar question.

I'm making a photo gallery using tags and would like to run something like:
Code:
SELECT * FROM images WHERE images.id IN (SELECT image_id FROM tags WHERE tags.tag IN ('tag1', 'tag2', 'tag3'))

Basically, the user can select a list of tags, and the page will show all images which have been tagged with that tag.

The "tags" table holds a list of "tags to images". Each image can be in the table multiple times (each time with a different tag) and each tag can be applied to multiple images.

Everything in that query is static (non changing), except the list of tags (which can be any length).
Is there any easy way to do this is active directory, or would I need to create my own select statement?
#5

[eluser]xzela[/eluser]
Hmmm...

I would use joins and transitional tables for a feature like that.
You'll need to create a table which holds the link between the images and the tags:
Code:
CREATE TABLE trans_table (
   id INT,
   image_id INT,
   tag_id INT,
   );
Then to see which tags are applied to each image you could run a query that would look something like this:
Code:
SELECT
   image.image_id,
   tag.tag_id,
   tag.tag_name
FROM
   image
   LEFT JOIN trans_table ON image.image_id = trans_table.image_id
   LEFT JOIN tag ON trans_table.tag_id = tag.tag_id
WHERE
   image.image_id = $image_id;

This will return all of the tags (names, ids) that applied to a specific image.

Let me know if you have any questions.
#6

[eluser]Darwin Survivor[/eluser]
Actually, I'm set up a little differently.

My tags table is equivalent to what you are calling trans_table. I know it's probably not the most space efficient, but my tag table actually has the image_id and the actual tag (varchar). I am allowing the user to type in ANY tag, not just select from pre-defined ones.

You example, while nice, kind of did the opposite of what I'm looking for. I don't need to know which tags apply to a specific image, but which images have a specific tag (or set of tags) applied to it.

Sorry if my first post was a little confusion, but basically the user is selecting a list of tags (ex: cat, dog, Christmas) and the site shows them all the pictures that have been tagged with any of those tags (ex: pictures of cats, dog, Christmas or any combination of them).

Currently I have the query hard-codded, using a join to create the section after 'IN', but I would like to use active directory to make the code a little easier to read and understand.




Theme © iAndrew 2016 - Forum software by © MyBB