Zend Framework advanced WHERE IN query

Question

So, i've come up with this query wich works perfectly fine when executed. However, since I want to use the pagination from Zend, the query has to be through an adapter.

This is the query that has to be converted: (i've cut the query so only the important part is here. I am aware of the fact that you can't run this query LOL)

//snip
WHERE
    city IN (
        SELECT 
            Plaatsnaam
        FROM
            plaatsnamen
        WHERE
            Latitude 
                BETWEEN 
                    ?
                AND 
                    ?
        AND
            Longitude
                BETWEEN
                    ?
                AND
                    ?       
    )
//snip

Works perfectly fine! However.. As you can see, i'm using an "advanced" WHERE IN case. Somehow, i can't get this converted and i'm stuck.

This is what it has to be converted to:

$db = Zend_Db_Table::getDefaultAdapter();                
$select = $db->select('gethousecity.id, city')
                                ->from('gethousecity')
                                    ->join('houses_props', 'houses_props.id = gethousecity.id')
                                ->where('city IN ()') // HOW TO CONVERT THE UPPER QUERY TO FIT IT HERE?
                                ->where('is_online = 1')
                                ->where('houses_props.prop_name = ?', 'something')
                                ->where('houses_props.prop_value BETWEEN ? AND ?', array(1,2));

I haven't found any documentation at all on how to convert the query into some format that fits here. Does anyone have any idea? Mostly because I need to insert some values and ZF is rewriting some weird shit there.

I'm kinda stuck now, any help would be awesome!

Tomáš Fejfar · Accepted Answer · 2012-05-14 19:31:48Z

3

Subqueries work just fine.

$select1->cols('id')->where('date > NOW()');
$select2->where('id IN (?)', $select1);

If the problem is the complex where, than you can use named parameters.

$select1 = $db->select();
$select2 = $db->select();
$select1->from('plaatsnamen', array('Plaatsnaam'))
        ->where('Latitude BETWEEN :latmin AND :latmax')
        ->where('Longitude BETWEEN :longmin AND :longmax');
$select2->from('gethousecity')
        ->join('houses_props', 'houses_props.id = gethousecity.id')
        ->where('city IN ?', $select1) // HOW TO CONVERT THE UPPER QUERY TO FIT IT HERE?
        ->where('is_online = 1')
        ->where('houses_props.prop_name = ?', 'something')
        ->where('houses_props.prop_value BETWEEN :propsmin AND :propsmax');
die($select2);

Returns:

SELECT `gethousecity`.*, `houses_props`.*
FROM `gethousecity`
INNER JOIN `houses_props` ON houses_props.id = gethousecity.id
WHERE 
    (city IN (
        SELECT `plaatsnamen`.`Plaatsnaam`
        FROM `plaatsnamen`
        WHERE 
        (Latitude BETWEEN :latmin AND :latmax) AND
        (Longitude BETWEEN :longmin AND :longmax)
    )) AND
    (is_online = 1) AND
    (houses_props.prop_name = 'something') AND
    (houses_props.prop_value BETWEEN :propsmin AND :propsmax)

Which should work with named parameters, as seen in manual

$bind = array(
    ':latmin' => 10,
    ':latmax' => 3,
    '...' => '...',
);
$db->fetchAll($select2, $bind);

edited May 14, 2012 at 19:31

answered May 14, 2012 at 14:51

Tomáš Fejfar

11.2k8 gold badges58 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ChrisH Over a year ago

Yes, for those simple matters is does. Not when you have a subquery.

ChrisH Over a year ago

Yeah, that works as well! That's the weird thing, it works for named parameters. I'm upvoting your answer since you make use of the $db. In the end, both of our answers are correct!

Tomáš Fejfar Over a year ago

But the main problem were the named parameters? You can easily create your own Db adapter and override the select. We use it to inject language suffixes to table cols - i.e.: change every {anything}_en to {anything}_de for user with german language.

Bolebor Over a year ago

You can also bind named parameters during creating select: $select->bind(array(':latin' => 10, ':latex' => 3));

ChrisH · Accepted Answer · 2012-05-14 01:58:18Z

In the end, as a quick workaround, i came up with the following; $db->quote.

$select = $db->select('gethousecity.id, city')
                                    ->from('gethousecity')
                                        ->join('houses_props', 'houses_props.id = gethousecity.id')
                                    ->where('city IN (  
                                                    SELECT Plaatsnaam
                                                    FROM plaatsnamen
                                                    WHERE Latitude BETWEEN '.$latlon_search[0].' AND '.$latlon_search[1].'
                                                        AND Longitude BETWEEN '.$latlon_search[2].' AND '.$latlon_search[3].'
                                    )') 
                                    ->where('is_online = 1')
                                    ->where('houses_props.prop_name = ?', 'vraagprijs[vraagprijs]')
                                    ->where('houses_props.prop_value BETWEEN '.$db->quote($price_start).' AND '.$db->quote($price_end));

For me, it was the only way to make this work. Every time i did:

->where('statement ? AND ?', array($val1, $val2)

The result was this:

statement val1,val2 AND val1,val2

Ezequiel Muns · Accepted Answer · 2012-05-14 02:03:27Z

0

As far as I know, ZF DB does not have native support for subqueries.

You could try to do this:

// snip
->where("city IN (
    SELECT Plaatsnaam
    FROM plaatsnamen
    WHERE Latitude BETWEEN $lowLat AND $highLat
      AND Longitude BETWEEN $lowLon AND $highLon
)")
// snip

EDIT: Interpolating the values directly into the condition string, this is safe only if the variables are trusted (or if you got them from the user, make sure you cast them to floats before doing this).

Alternatively, you could try to avoid the subquery, and instead do a join but this may have performance implications (maybe for the better or worse).

edited May 14, 2012 at 2:03

answered May 14, 2012 at 1:42

Ezequiel Muns

7,78035 silver badges59 bronze badges

7 Comments

ChrisH Over a year ago

Well, i've thought of that as well. However, it doesn't work. ZF rewrites the query in such a weird way: pastebin.com/veCSzj6A

Ezequiel Muns Over a year ago

@SiteSafeNL Can you post the resultant query?

ChrisH Over a year ago

The 4 values are pasted at once, in stead of one by one.. It doesn't make any sense to me

Ezequiel Muns Over a year ago

I just looked through the Zend_Db_Select code and the where() function only takes a single parameter, i.e. every ? in the clause gets quoted with the same value. The other option is to quote the values directly into the clause. Updating my answer to reflect this.

ChrisH Over a year ago

Indeed, and that's a design fault in my honest opinion :/

|

Mr Coder · Accepted Answer · 2012-05-14 11:11:23Z

0

Try this

 $db = Zend_Db_Table::getDefaultAdapter();  
    $selectInner = $db->select()->from('plaatsnamen','Plaatsnaam')
                   ->where("Lattitude BETWEEN $lowLat AND $highLat")
                   ->where("Longitude BETWEEN $lowLon AND $highLon")
                   ->__toString();



    $select = $db->select('gethousecity.id, city')
                                    ->from('gethousecity')
                                        ->join('houses_props', 'houses_props.id = gethousecity.id')
                                    ->where("city IN ($selectInner)");

answered May 14, 2012 at 11:11

Mr Coder

8,2065 gold badges49 silver badges74 bronze badges

1 Comment

ChrisH Over a year ago

You should definitly quote the vars for safety matters.

Collectives™ on Stack Overflow

Zend Framework advanced WHERE IN query

4 Answers 4

4 Comments

Comments

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related