where_not_in (Solved)

#1
[eluser]Maglok[/eluser]
Lets say I have a array of objects that I got by using the active record class.
I have a second array of objects.

Is there anyway that I can substract one from the other? (Array1 - array2)

Example: I can easily get all open events and all the events someone signed up for. I want to also list those a person did not sign up for (yet) though.

Kind of hard to explain really. I cannot phantom a query that would let me select just the ones a person did not sign up for (since the database only records those that he DID sign up for).

#2
[eluser]jedd[/eluser]
Did you try using array_diff()?

I'm assuming that Array2 is, always, a superset of Array1 in this instance?

Array messing usually provides much scope for confusion - I always end up writing a test function with a fair bit of data to confirm it's really doing what it's promising to do. Not sure if the arrays need to be sorted prior to hand-over to array_diff(), or if it does an exhaustive loop-within-a-loop (I'd imagine the latter).

#3
[eluser]wiredesignz[/eluser]
Certainly, try http://php.net/manual/en/function.array-diff.php

#4
[eluser]Maglok[/eluser]
That's very interesting, but if I grab a result set with the active records class I get a array of objects and somehow PHP thinks the following of using array_diff on that.

Code:
Message: array_diff() [function.array-diff]: Argument #1 is not an array

#5
[eluser]jedd[/eluser]
Yeah, I noticed that the function description says it converts the array entry to a string - presumably if there's n-dimensional arrays you're dealing with it just encapsulates the whole thing. CI objects tend to be pretty large, so I'm not sure this will be a good idea (sure you don't want to use an array of arrays? Wink but you might want to convert your array of objects to an array of serialised objects, and then try the diff again?

Sure you can't change your SELECT to cope with a 'NOT IN...' clause? It might be easier, as well as faster.

#6
[eluser]Maglok[/eluser]
The query is kinda this for those that someone HAS signed up for:

Code:
SELECT `evenementen`.`naam`, `evenementen`.`subtitel` FROM `inschrijvingen`, `evenementen` WHERE `inschrijvingen`.`persoon_id` = 1 AND `inschrijvingen`.`evenement_id` = `evenementen`.`id` ORDER BY `evenementen`.`naam`, `evenementen`.`subtitel` asc

Using the active record class then switching to arrays while the rest of the app uses objects just seems very messy to me.

I get that query by doing this:

Code:
/*****
     * Haal alle evenementen op waarvoor een persoon al is ingeschreven.
     * @param    persoon_id    - ID van de persoon
     * @return    Array van evenementen
     */
    function get_al_ingeschreven($persoon_id) {
        $this->db->select('evenementen.id, evenementen.naam, evenementen.subtitel');
        $this->db->from('inschrijvingen, evenementen');
        $this->db->where('inschrijvingen.persoon_id', $persoon_id);
        $this->db->where('inschrijvingen.evenement_id', 'evenementen.id', FALSE);
        $this->db->order_by('evenementen.naam, evenementen.subtitel','asc');

        return $this->db->get();
    }

#7
[eluser]jedd[/eluser]
Agreed about the benefits of consistency.

Looks like you've got two approaches then - either modify your selection criteria, removing the items already subscribed to - and you can do that either by running a select query first, and massaging the output into something you can plug into the query, or do an encapsulated (SELECT...) within the one query, and put the resultant data in a 'NOT IN (...)' construct.

Second option is to try to process your array of objects into an array of serialise()'d objects, and then do the array_diff(), and see what happens. CI objects contain lots of surprises (try doing a var_dump of one from each of your arrays you're playing with now, say, and seeing if there's something obvious and fixable).

#8
[eluser]Maglok[/eluser]
I took your advice and wrote a little query:
Code:
SELECT `id`, `naam`, `subtitel` FROM `evenementen` WHERE `id` NOT IN (SELECT `inschrijvingen`.`evenement_id` FROM `inschrijvingen`, `evenementen` WHERE `inschrijvingen`.`evenement_id` = `evenementen`.`id` AND `inschrijvingen`.`persoon_id` = <variable>) ORDER BY `naam`, `subtitel` asc

Now the problem is how do I translate this into active record speak? $this->db->where_not_in(); doesn't seem to be the thing I am looking for.

Atm I got this:
Code:
/*****
     * Haal alle open evenementen op waarvoor een persoon nog niet is ingeschreven.
     * @param    persoon_id    - ID van de persoon
     * @return    Array van evenementen
     */
    function get_niet_ingeschreven($persoon_id) {
        $this->db->select('id, naam, subtitel');
        $this->db->from('evenementen');
                //NOT IN THING
        $this->db->order_by('naam, subtitel','asc');

        return $this->db->get();
    }

#9
[eluser]Maglok[/eluser]
After some experiments and some talking to a CI expert I know I got it. Turns out there is not a lot on the forum about where_not_in().

Code:
/*****
     * Haal alle open evenementen op waarvoor een persoon nog niet is ingeschreven.
     * @param    persoon_id    - ID van de persoon
     * @return    Array van evenementen
     */
    function get_niet_ingeschreven($persoon_id) {
        $group = $this->get_al_ingeschreven($persoon_id);
        $not[] = '0';
        foreach($group->result() as $row) {
            $not[] = $row->id;
        }

        $this->db->select('id, naam, subtitel');
        $this->db->from('evenementen');
        $this->db->where_not_in('id', $not);
        $this->db->order_by('naam, subtitel','asc');

        return $this->db->get();
    }

    /*****
     * Haal alle evenementen op waarvoor een persoon al is ingeschreven.
     * @param    persoon_id    - ID van de persoon
     * @return    Array van evenementen
     */
    function get_al_ingeschreven($persoon_id) {
        $this->db->select('evenementen.id, evenementen.naam, evenementen.subtitel');
        $this->db->from('inschrijvingen, evenementen');
        $this->db->where('inschrijvingen.persoon_id', $persoon_id);
        $this->db->where('inschrijvingen.evenement_id', 'evenementen.id', FALSE);
        $this->db->order_by('evenementen.naam, evenementen.subtitel','asc');

        return $this->db->get();

Thanks for the help. Hope this helps people that are in the same situation. Problem solved!

#10
[eluser]sl3dg3hamm3r[/eluser]
Hmmm, I didn't try out to make that run with active record (without having two queries). But if it is really not possible, I'd rather head to leave active record away and do this with one nicely formatted sql-query.
In such cases I ask myself: "how often will I swap the persistance layer to a completely else system? mhmm... 99.9% I'll stay with mysql" :-)


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.