Rowboat with where condition

372 Posts

Steeve Reply #1, 10 years, 5 months ago

Hi,

I got a problem with Rowboat (getPage, getResources) where condition, if i call rowboat like this

$params['element'] = 'Rowboat';
$params['table'] = 'aw2c_annonces';
$params['sortBy'] = 'date_offre:desc';
$params['where']= '{"NB_PIECES:LIKE":"%5%"}';
$params['totalVar'] = 'rowboat.total';
$params['tpl'] = 'listingBienTpl';
$params['cache'] = '0';
$params['cacheResults'] = '0';
$params['limit'] = '0';

return $modx->runSnippet('getPage', $params);

This work !

But if i call like with all possibilities from getResource snippet:

$operators = array(
    '<=>' => '<=>',
    '===' => '=',
    '!==' => '!=',
    '<>' => '<>',
    '==' => 'LIKE',
    '!=' => 'NOT LIKE',
    '<<' => '<',
    '<=' => '<=',
    '=<' => '=<',
    '>>' => '>',
    '>=' => '>=',
    '=>' => '=>'
);

Exemple:

$params['where']= '{"NB_PIECES:=":"%5%"}';

This return where = 0

$params['where']= '{"NB_PIECES:==":"%5%"}';

OR

$params['where']= '{"NB_PIECES:===":"%5%"}';

OR

$params['where']= '{"NB_PIECES:>=":"%5%"}';

This return nothing.

I really don't know why operator can be just LIKE, NO LIKE ?!

This question has been answered by multiple community members. See the first response.

discuss.answer

5,888 Posts

Bruno17 Reply #2, 10 years, 5 months ago

you can't use this operators with rowboat in the where-property.
This operators, which you are talking about are only for getResources - tvFilters.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

discuss.answer

463 Posts

Tyreal2012 Reply #3, 10 years, 5 months ago

Isnt the % a wildcard? so you can never have equals a wildcarded value (i know its that way in Oracle)

$params['where']= '{"NB_PIECES:>=":"5"}';

Should work just fine - i tested it just using a rowboat call that i have on a site (admittedly running straight from a DB)

[[Rowboat?
&table=`table`
&tpl=`tpl`
&where=`{"id:>":"60"}`
&limit=`100`
&sortBy=`type:ASC`
]]

discuss.answer

372 Posts

Steeve Reply #4, 10 years, 5 months ago

Thanks Bruno for your quickly reply.

I found and solve the problem, it was simply because i surround the operand with %.
We can just use %1% if the operator is LIKE or NOT LIKE. If you use symbol in operator like =,>=,=<,!=... we have to take off the % in operand.

I hope this will help if someone got the same problem ;-)

372 Posts

Steeve Reply #5, 10 years, 5 months ago

Thanks you aswell Tyreal ! Sorry i was written the solution at the same time as you ;-)

372 Posts

Steeve Reply #6, 10 years, 5 months ago

Sorry but i got another problem with where in Rowboat :

This work !

$params['where']= '{"TOWN":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%"}';

But this doesn't !

$params['where']= '{"TOWN":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%","NUMBER_ROOMS:LIKE":"%4%"}';

$params['where']= '{"TOWN":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%","AND:NUMBER_ROOMS:LIKE":"%4%"}';

$params['where']= '{"TOWN":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%","NUMBER_ROOMS:=":"4"}';

Thanks in advance.

463 Posts

Tyreal2012 Reply #7, 10 years, 5 months ago

$params['where']= '{"TOWN":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%","NUMBER_ROOMS:LIKE":"%4%"}';

You need the LIKE value in the first instance

372 Posts

Steeve Reply #8, 10 years, 5 months ago

Quote from: Tyreal2012 at Apr 27, 2016, 09:45 AM

$params['where']= '{"TOWN":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%","NUMBER_ROOMS:LIKE":"%4%"}';
You need the LIKE value in the first instance

Thanks again for reply !!!

This work ! And i get 5 results

$params['where']= '{"TOWN:LIKE":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%"}';

But when i had another parameters like :

$params['where']= '{"TOWN:LIKE":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%","NUMBER_ROOMS:LIKE":"%4%"}';

OR

$params['where']= '{"TOWN:LIKE":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%","NUMBER_ROOMS:=":"4"}';

I get only 4 results and numbers of rooms 1,4,5 ?!

If i test only the new parameters alone it works :

$params['where']= '{"NUMBER_ROOMS:=":"4"}';

OR

$params['where']= '{"NUMBER_ROOMS:LIKE":"%4%"}';

I have no idea ! Thanks in advance for help.

463 Posts

Tyreal2012 Reply #9, 10 years, 5 months ago

Why would the number of rooms be a Like rather than a set quantity, location sure, but rooms in my opinion would be fixed values i.e > 4 or = 4

I dont see why

$params['where']= '{"TOWN:LIKE":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%","NUMBER_ROOMS:=":"4"}';

Doesnt work, have you tried:

$params['where']= '{"NUMBER_ROOMS:=":"4","TOWN:LIKE":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%"}';

just incase the AND is messing up

372 Posts

Steeve Reply #10, 10 years, 5 months ago

I try this (with and without AND):

$params['where']= '{"NUMBER_ROOMS:=":"4","TOWN:LIKE":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%"}';
$params['where']= '{"NUMBER_ROOMS:LIKE":"%4%","TOWN:LIKE":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%"}';
$params['where']= '{"NUMBER_ROOMS:=":"4","AND:TOWN:LIKE":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%"}';
$params['where']= '{"NUMBER_ROOMS:LIKE":"%4%","AND:TOWN:LIKE":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%"}';
$params['where']= '{"TOWN:LIKE":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%","AND:NUMBER_ROOMS:LIKE":"%4%"}';
$params['where']= '{"TOWN:LIKE":"%LE BEAUSSET%","OR:TOWN:LIKE":"%OLLIOULES%","AND:NUMBER_ROOMS:=":"4"}';

So i try to made it with only 2 parameters :

$params['where']= '{"VILLE_OFFRE:LIKE":"%LE BEAUSSET%","AND:NB_PIECES:LIKE":"%4%"}';
$params['where']= '{"VILLE_OFFRE:LIKE":"%LE BEAUSSET%","AND:NB_PIECES:=":"4"}';

Then i return back to the getResources snippet to see if i can get an exemple of where statement and i found this:

&where=`{{"alias:LIKE":"foo%", "OR:alias:LIKE":"%bar"},{"OR:pagetitle:=":"foobar", "AND:description:=":"raboof"}}`

Then i try other configurations :

$params['where']= '{{"TOWN:LIKE":"%LE BEAUSSET%"},{"NUMBER_ROOMS:=":"4"}}';
$params['where']= '{{"TOWN:LIKE":"%LE BEAUSSET%"},{"AND:NUMBER_ROOMS:=":"4"}}';
$params['where']= '{{"TOWN:LIKE":"%LE BEAUSSET%"},{"NUMBER_ROOMS:LIKE":"%4%"}}';
$params['where']= '{{"TOWN:LIKE":"%LE BEAUSSET%"},{"AND:NUMBER_ROOMS:LIKE":"%4%"}}';

I try a lot of configurations before asking in the forum !
Nothing work and i need to had 4 more parameters. I don't know if it will be possible...

Answered Rowboat with where condition