Query Builder .between() Usage

Questions and answers regarding general SQL and backend databases

Query Builder .between() Usage

Postby kwpsd » Tue Feb 20, 2018 1:57 am

Servoy 8.2.1

How do you convert the following simplified SQL statement into Query Builder code using the .between() function?

Code: Select all
SELECT date_start, date_end
FROM table
WHERE ? BETWEEN date_start AND date_end

var args = [ dateTest ]


We tried the QB code below, but a warning is emitted.

Code: Select all
var query = datasources.db.database.table.createSelect()

query.params[ 'dateTest' ] = dateTest

query.where.add( query.getParameter( 'dateTest' ).between( query.columns.date_start, query.columns.date_end ) )


The method between() is undefined for the type QBParameter<db:/database/table>

Any ideas?
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Query Builder .between() Usage

Postby mnaeimi » Tue Feb 20, 2018 7:47 pm

Hi Kim,

This is an example:
var query = datasources.db.example.orders.createSelect();
query.result.addPk();
query.where.add(query.columns.date_order.between(date_start,date_end))


I recommend you watch this video: https://www.youtube.com/watch?v=WxPqhJZtr4A . It is about servoy QB.

Best,
Mina
Mina @ Servoy
mnaeimi
 
Posts: 26
Joined: Fri Jun 08, 2012 2:48 pm

Re: Query Builder .between() Usage

Postby kwpsd » Wed Feb 21, 2018 6:13 am

Hi, Mina.

Thank you for responding.

I probably did not make myself clear in describing the problem. dateTest is a Javascript variable, not a table column name. dateStart and dateEnd are table column names. I want to select all records in the table where dateTest falls between dateStart and dateEnd. The SQL where clause works...I am trying to convert the SQL to a QB object using the .between() function. I am guessing that what I am attempting is not supported in QB, but thought I would post to see if someone had a solution.
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Query Builder .between() Usage

Postby patrick » Wed Feb 21, 2018 11:43 am

The query build between function asks whether a column's value is between two given values, so not what you want here.

Why not simply rephrase your query to

Code: Select all
SELECT date_start, date_end
FROM table
WHERE date_start <= ? AND date_end >= ?


that one is easily doable with QueryBuilder then.
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Query Builder .between() Usage

Postby ROCLASI » Wed Feb 21, 2018 11:52 am

However, you can't pass (SQL) object names as prepared statement parameters.
But I think, since you would use some (solution) code logic anyway to change your SQL to use the specific column name you can do the same to pass the proper QB column object to your QB Select.
Or am I missing something?
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Query Builder .between() Usage

Postby rgansevles » Thu Feb 22, 2018 1:08 am

Kim,

This should give the same result:

Code: Select all
var query = datasources.db.database.table.createSelect()

query.where
   .add(query.columns.date_start.le(query.getParameter( 'dateTest' ))
   .add(query.columns.date_end.ge(query.getParameter( 'dateTest' ))

query.params[ 'dateTest' ] = dateTest


Note that you have to set the parameter value after defining the parameter in the query, not before.

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Query Builder .between() Usage

Postby kwpsd » Fri Feb 23, 2018 2:45 am

Thank you all for responding...I really appreciate your feedback!

...asks whether a column's value is between two given values...


Too bad QB won't take an external value like in my original SQL where clause. The only reason I tried to make dateTest a QB parameter was to use the QB .between() function, but it appears the function is not supported for parameters. For the alternate query, I think I would just skip the parameter business and pass the variable directly:

Code: Select all
query.where
   .add( query.columns.date_start.le( dateTest )
   .add( query.columns.date_end.ge( dateTest )


Note that you have to set the parameter value after defining the parameter in the query, not before.


What is the reason for the precedence? I thought you could manipulate the query object in any way until it is executed (e.g. .getDataSetByQuery( query, -1 )).
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Query Builder .between() Usage

Postby rgansevles » Fri Mar 23, 2018 11:59 am

What is the reason for the precedence? I thought you could manipulate the query object in any way until it is executed (e.g. .getDataSetByQuery( query, -1 )).


Code: Select all
query.params[ 'dateTest' ] = dateTest

Looks for a parameter with that name and sets is value, it does not create it.
I guess we could support it the other order as well if people would need that.

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Query Builder .between() Usage

Postby kwpsd » Fri Mar 23, 2018 6:44 pm

I would like to see this implemented for the QB between function...it makes the code simpler and cleaner, and I believe it would be useful for other developers. Of course, this is just my opinion.
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 6 guests