[doctrine][mysql] filteren, probleem met between

[rewind.]

Ik heb een probleem bij het terugkrijgen van de juiste data.

Schema:
rent_item
title
description
etc..

rent_item_option
option_type_id (bv. kmstand of provincie)
option_id (bv. groningen)
rent_item_id
value (bv. de waarde van de kmstand: 150000)

Nu bouw ik de query op in doctrine en kom ik op de volgende code (echte code genereerd onderstaande DQL query):

PHP:

$q = Doctrine_Query::create()
->select('r.*, ro.*')
->from('RentItem r')
->leftJoin('r.RentItemOption ro')
->where('ro.option_id IN (15,16,18)')
->andWhere('ro.option_id = 9')
->andWhere('(ro.option_type_id = 1 AND ro.value BETWEEN 0 AND 200000)')
->andWhere('(ro.option_type_id = 2 AND ro.value BETWEEN 2001 AND 2010)')
->groupBy('r.id');

dit resulteert in de volgende mysql query:

SQL:

SELECT m.id AS m__id, m.user_id AS m__user_id, m.type AS m__type, m.title AS m__title, m.description AS m__description, m.defaultprice AS m__defaultprice, m.phonenumber AS m__phonenumber, m.faxnumber AS m__faxnumber, m.mobilenumber AS m__mobilenumber, m.website AS m__website, m.longitude AS m__longitude, m.latitude AS m__latitude, m.created_at AS m__created_at, m.updated_at AS m__updated_at, m.slug AS m__slug, m2.id AS m2__id, m2.option_type_id AS m2__option_type_id, m2.option_id AS m2__option_id, m2.rent_item_id AS m2__rent_item_id, m2.value AS m2__value
FROM mo_rent_item m
LEFT JOIN mo_rent_item_option m2 ON m.id = m2.rent_item_id
WHERE (
m2.option_id = 9
AND
m2.option_id
IN ( 15, 16, 19 )
AND (
m2.option_type_id =1
AND m2.value
BETWEEN 0
AND 200000
)

AND (
m2.option_type_id =2
AND m2.value
BETWEEN 2001
AND 2010
)
)
GROUP BY m.id

Deze geeft altijd 0 resultaten. Heeft te maken met de between die ook afhankelijk is van de option_type_id. Gebruik ik echter een OR voor de 'betweens' dan werkt het wel maar zijn de resultaten verkeerd.

Ik kan het oplossen in php zelf maar wil het liefst alles in 1 query, hoe bouw ik deze nou goed op?

[Acid_Burn]

Dat je 0 results krijgt is wel duidelijk.. r.option moet en (15, 16 of 18) zijn en 9 zijn .. en (1 of 2) met wat andere voorwaarden. Dat gaat natuurlijk niet gebeuren. Schrijf eerst eens op wat je precies wil hebben.

[ Voor 23% gewijzigd door Acid_Burn op 06-07-2011 22:59 ]

[CodeCaster]

Dat moet inderdaad een OR zijn lijkt me, en gaat dat werken met die group by?

[rewind.]

nee de and klopt 1 rentitem heeft ongeveer 20 regels in rent_item_option (opties dus). Ik vraag alle rent_items op die die voldoen aan de filter.

Dus bv. alle rent_items met als optie groningen of drenthe (dat is de IN) EN een optie diesel. Dat stukje werkt helemaal zelfs met de group by waar ik ook mijn twijfels over had, het gaat pas fout bij het stukje met de beween die ook moet voldoen aan de option_type_id die bepaald welke filter het is (de kmstand of jaartal).

[Acid_Burn]

Toch is het dit stukje wat er voor zorgt dat je 0 results krijgt. Die waarde kan gewoon nooit aan allebei de voorwaarden voldoen.

code:

->where('ro.option_id IN (15,16,18)')
->andWhere('ro.option_id = 9')

[Voutloos]

Per optie type zal je een join met de option tabel moeten doen.

[Reinier]

Los daarvan groepeer je op 1 veld en selecteer je er veel meer, dat is ook niet helemaal juist

[RobIII]

Voutloos schreef op woensdag 06 juli 2011 @ 23:14:
Per optie type zal je een join met de option tabel moeten doen.

Juist; en ik zou de voorwaarden op de join zelf zetten daarvoor (dus join foobar on foo = bar and x = y).

Reinier schreef op woensdag 06 juli 2011 @ 23:15:
Los daarvan groepeer je op 1 veld en selecteer je er veel meer, dat is ook niet helemaal juist

Ook dat is juist: Hoe werkt dat GROUP BY nu eigenlijk?

[ Voor 37% gewijzigd door RobIII op 06-07-2011 23:16 ]

[Voutloos]

Overigens heb je natuurlijk ook gewoon last van een dramatisch datamodel. Waren kmstand, locatie of bouwjaar nog niet uitgevonden toen je begon met het opslaan van autodata?

[rewind.]

RobIII schreef op woensdag 06 juli 2011 @ 23:16:
[...]

Juist; en ik zou de voorwaarden op de join zelf zetten daarvoor (dus join foobar on foo = bar and x = y).

[...]

Ook dat is juist: Hoe werkt dat GROUP BY nu eigenlijk?

Inderdaad, hier ga ik mee aan de gang!
Wat betreft group by zal ik ook aanpassen.

Voutloos schreef op woensdag 06 juli 2011 @ 23:20:
Overigens heb je natuurlijk ook gewoon last van een dramatisch datamodel. Waren kmstand, locatie of bouwjaar nog niet uitgevonden toen je begon met het opslaan van autodata?

Het gaat niet alleen om auto's, rentitems kunnen verschillende dingen zijn die bv. Geen kmstand hebben. De opties moeten van voor naar achter beheerbaar zijn. Locatie, volgorde, single of multiple waardes etc.

wat voor datamodel zouden jullie kiezen voor een filter?

[ Voor 38% gewijzigd door rewind. op 07-07-2011 08:21 ]

[rewind.]

Ik heb het nu als volgt opgelost:

PHP:

<?php
$q = Doctrine_Query::create()
->select('r.id, COUNT(r.id) AS numconditions')
->from('RentItem r')
->leftJoin('r.RentItemOption ro')
->where('ro.option_id IN (15,16,18)')
->orWhere('ro.option_id = 9')
->orWhere('(ro.option_type_id = 1 AND ro.value BETWEEN 0 AND 200000)')
->orWhere('(ro.option_type_id = 2 AND ro.value BETWEEN 2001 AND 2010)')
->groupBy('r.id');
->having('numconditions >= 4');
?>

[Voutloos]

rewind. schreef op donderdag 07 juli 2011 @ 08:16:
Het gaat niet alleen om auto's, rentitems kunnen verschillende dingen zijn die bv. Geen kmstand hebben. De opties moeten van voor naar achter beheerbaar zijn. Locatie, volgorde, single of multiple waardes etc.

wat voor datamodel zouden jullie kiezen voor een filter?

In dat geval kan je datamodel en de query die je post prima hoor. Als je er maar op let dat als alle items wel een eigenschap (waar de definitie van vast staat) gemeen hebben, dat je die gewoon wel in de item tabel op neemt.

[rewind.]

uiteindelijk komt er nog een option_group boven die de option types groepeert zodat iedere rentitem zijn eigen groepje opties heeft die passen bij de rentitem.

[Acid_Burn]

Waarom maak je van dit

PHP:

->where('ro.option_id IN (15,16,18)')
->orWhere('ro.option_id = 9')

niet dit:

PHP:

->where('ro.option_id IN (9, 15,16,18)')

[rewind.]

Acid_Burn schreef op zaterdag 09 juli 2011 @ 20:03:
Waarom maak je van dit

PHP:

->where('ro.option_id IN (15,16,18)')
->orWhere('ro.option_id = 9')

niet dit:

PHP:

->where('ro.option_id IN (9, 15,16,18)')

Goed punt, komt omdat de query nu automatisch wordt opgebouwt per option_type_id. Dit is natuurlijk gewoon samen te voegen dus zal ik aanpassen