How can I create this query with builder

[s.alpaslan]

Hello there ,
I’m creating booking system with wappler . But I need to list all available rooms . I have a query like this

SELECT * FROM rooms WHERE id NOT IN (SELECT * FROM events WHERE ( start<= '$dateout' AND end >= '$datein )

How can I create this query in query builder.

Please guide me …

[mrbdrm]

im guessing you have to split it into 2 query

[t11]

You have to use a repeat action.

[patrick]

The query builder doesn’t support nested queries, so you need to split it in 2 queries.

First the sub query, I think the * should be something like room_id

SELECT room_id FROM events WHERE start<= {{dateout}} AND end >= {{datein}}

The second query will use the result from the first query and use the values formatter to get only an array with all the ids for the IN filter

SELECT * FROM rooms WHERE id NOT IN {{query1.values('room_id')}}

[s.alpaslan]

@patrick,

Result like this ;

{

* code: "42000",

* file: "C:\xampp\htdocs\hao2\dmxConnectLib\lib\db\Connection.php",

* line: 54,

* message: "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1",

* trace: "#0 C:\xampp\htdocs\hao2\dmxConnectLib\lib\db\Connection.php(54): PDO->prepare('SELECT * FROM `...') #1 C:\xampp\htdocs\hao2\dmxConnectLib\modules\dbconnector.php(67): lib\db\Connection->execute('SELECT * FROM `...', Array) #2 C:\xampp\htdocs\hao2\dmxConnectLib\lib\App.php(159): modules\dbconnector->select(Object(stdClass), 'query1') #3 C:\xampp\htdocs\hao2\dmxConnectLib\lib\App.php(128): lib\App->execSteps(Object(stdClass)) #4 C:\xampp\htdocs\hao2\dmxConnectLib\lib\App.php(98): lib\App->execSteps(Array) #5 C:\xampp\htdocs\hao2\dmxConnectLib\lib\App.php(71): lib\App->exec(Object(stdClass)) #6 C:\xampp\htdocs\hao2\dmxConnect\api\test\AA.php(8): lib\App->define(Object(stdClass)) #7 {main}"

}

![2|690x393]
(upload://oUPHDoFvVdHI0J5vyI5vuAOI2Qs.png)

mysql syntax error …
where is wrong ? can you guide me

[psweb]

I think you are almost there, set a repeat step after query2 and use the expression as query2, add query1 inside the repeat and set the condition to {{query2.urun_id}}

So the logic here is that the first query needs a repeat to run through every single record of the query then pass all records found to the secondary query in order to check the condition.

[s.alpaslan]

yeap , there is no problem with repeat … its done …

I think the problem split does not work or syntax error …

[s.alpaslan]

string - array conversation error…

[psweb]

have you already tried with a repeat already? Without it the secondary query has no way to get all the records, only one record, it needs more than one record to create an array, so if it only had one then maybe it can not create the array correctly, I think it might be at least worth a try, it might surprise you.

[s.alpaslan]

yeap working …

but I cant use “in” or “not in” with query builder … I dont know …

[s.alpaslan]

this is output of query1 (all booked rooms )

but I cant use it via repeat region with in or not in

[psweb]

Well I am glad the first part seems to be working now without the syntax error anymore, for the in or not in issue, can you show me the contents of both of your queries urunler and rezervasyonlar

[psweb]

Are you accessing the same table in both of those queries or 2 different tables, i can not tell from your initial screenshots

[s.alpaslan]

2 different tables … I will sent screen shot of queries …

[mrbdrm]

@psweb just a note. you don’t need the repeat if you only want one record just access it with query[0].column

[s.alpaslan]

inside of query 2

inside of query 1

inside of query 1 conditions tab

[psweb]

Thanks @mrbdrm, do you know if he only needs to access a single record, I wasn’t sure, if he only needs the one then I agree that it would not need the repeat.

[s.alpaslan]

I want to list all of available rooms

urunler tables mean rooms table
events tables mean booked reservations

[psweb]

if you click the lightening bolt next to the ‘not in’ condition can you show me what available choices there are in one more screenshot

[s.alpaslan]

@psweb

if you click the lightening bolt next to the ‘not in’ condition can you show me what available choices there are in one more screenshot