Filtering a MySQL query with multiple conditions

Question

I have my output array named "Feed Items" (fi):

(
    [0] => Array
        (
            [reg] => 2015-08-03 13:39:00
            [id] => fd7ec4107d16b07c1a13cbdd386af8d2cb05ffca
            [user_id] => de5fd44db1760b006b1909cf1db11a78b38e455c
            [img] => edee88e88cf6e17732e393b5433cfd894662902e
            [type] => new_join_ambition
        )

)

I generate an array named "RemovedFeedItems" (rfi) that has the following structure:

(
    [0] => Array
        (
            [object_id] => fd7ec4107d16b07c1a13cbdd386af8d2cb05ffca
            [postee_id] => de5fd44db1760b006b1909cf1db11a78b38e455c
            [type] => new_join_ambition
        )

)

My mission is to not get the records that have the following condition:

ri.id == rfi.object_id && ri.user_id == rfi.postee_id && ri.type == rfi.type

The query that I use to get the Feed Items array is:

SELECT 
                i.registered AS reg, 
                a.id, 
                u.id AS user_id, 
                ui.image_id AS img,  
                'new_join_ambition' AS type, 
            FROM x_ambition_invites i 
                LEFT JOIN x_user u
                    ON i.to = u.id
                LEFT JOIN x_user_images ui 
                    ON u.id = ui.user_id
                LEFT JOIN x_ambitions a
                    ON i.ambition_id = a.id
            WHERE a.registered 
                BETWEEN '2014-07-21 14:25:03' AND '2015-08-05 12:04:41' 
            AND i.to != '8fa7a1679560876eaf2f8060abd916b692c719dc' 
            AND i.to IN ('de5fd44db1760b006b1909cf1db11a78b38e455c')

How can I adapt my query to implement the condition to remove the stated record from the FeedItems Array?

Thanks in advance.

Have you tried adding _AND NOT (id == object_id AND user_id == postee_id AND type == type)_ to the end of the where clause - qualifying the field names obviously (a.id etc) — PaulF, Aug 05 '15 at 11:20
@PaulF But the thing that needs to accounted for is that the latter conditions could be in a large array. So how can I essentially do the following: `(a.id == $a[n]['object_id'] AND a.user_id == $a[n]['postee_id'] AND a.type == $a[n]['type'])` through all the indexes of the removedFeedItems array — cwiggo, Aug 05 '15 at 11:25
Thanks for clarifying - thought it wouldn't be as simple as I suggested. — PaulF, Aug 05 '15 at 12:04

Little Santi · Accepted Answer · 2015-08-05T13:02:37.880

1

Try with an inline table:

SELECT ... FROM  ... WHERE (a,b,c) NOT IN (SELECT a1, b1, c1 UNION SELECT a2, b2, c2 UNION SELECT...)

The drawback is that you'll have to build a large query string. :-(

edited Aug 05 '15 at 13:02

answered Aug 05 '15 at 11:52

Little Santi

7,940
2
14
40

Do you know if this is the only solution available? :) – cwiggo Aug 05 '15 at 12:48
Hum... not sure, but taking in acount that MySQL does not support neither arrays nor user-defined datatypes... I can't think of anything else. – Little Santi Aug 05 '15 at 12:56
Of corse you could create and populate a temporary table, but you must take care to prevent concurrent access. Inline tables are 100% thread-safe. – Little Santi Aug 05 '15 at 12:57
Thanks. Pretty much think i've got it sussed. :) Just hope my users don't remove to many items! :P – cwiggo Aug 05 '15 at 12:58

Filtering a MySQL query with multiple conditions

1 Answers1