yes
to loop through the results and adding up to the variable value.
but these are wrong results … returned all ids
* urunler: [
* {
* id: 6,
* urun_adi: "ODA - 101",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 101",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 101",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 102",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 102",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 102",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 102",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 102",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 103",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 103",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 103",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "ODA - 103",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 103",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 103",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 104",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 104",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 104",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "ODA - 104",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 104",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 104",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 201",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 201",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 201",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "ODA - 201",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 201",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 201",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 202",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 202",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 202",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "ODA - 202",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 202",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 202",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 203",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 203",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 203",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "ODA - 203",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 203",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 203",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 204",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 204",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 204",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "ODA - 204",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 204",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 204",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 301",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 301",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 301",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "ODA - 301",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 301",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 301",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 302",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 302",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 302",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "ODA - 302",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 302",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 302",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 303",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 303",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 303",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "ODA - 303",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 303",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 303",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 304",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 304",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 304",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "ODA - 304",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 304",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 304",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 199",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 199",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 199",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "ODA - 199",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 199",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 199",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "ODA - 299",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "ODA - 299",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "ODA - 299",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "ODA - 299",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "ODA - 299",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "ODA - 299",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"},
* {
* id: 1,
* urun_adi: "211",
* start: "2018-01-01 14:00:00",
* end: "2018-01-03 18:00:00"},
* {
* id: 2,
* urun_adi: "211",
* start: "2018-01-07 00:00:00",
* end: "2018-01-10 00:00:00"},
* {
* id: 5,
* urun_adi: "211",
* start: "2018-08-15 00:35:10",
* end: "2018-08-25 00:35:10"},
* {
* id: 6,
* urun_adi: "211",
* start: "2018-08-26 00:35:10",
* end: "2018-08-26 01:35:10"},
* {
* id: 7,
* urun_adi: "211",
* start: "2018-08-28 00:35:10",
* end: "2018-09-27 00:35:10"},
* {
* id: 9,
* urun_adi: "211",
* start: "2018-09-29 12:35:10",
* end: "2018-12-27 00:35:10"}]
}That is very strange because the operator is set to not_equal, so it should have returned all results where only unique ids are returned, wow, ok, can you try change inner join to the other options and see what changes it makes in the returned results, so try right join and test, try left join and test please.
On the bright side at least you are not getting syntax errors anymore haha
yeap … bigest problem solved :))))))))
did you try array to string conversion ?
No I didn’t but I have done similar things to this before without it, i assume the array to string idea is the get the not in operator to work. As far as I know the not in operator is available on numbers and strings in server connect and that issue only exists in App Connect, but i could be wrong.
Have you tried all the join types to see if any gave better results
I tried before similar things too and its work perfect . as for me , there’s something we don’t know.
maybe wappler team can help …
I think unless the Wappler staff have suggestions one of the best users to ask with this type of issue might be @TomD because i know he spends lots of time messing with database queries. I have unfortunately run out of great ideas here.
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')}}
@patrick , maybe check this issue ..
@psweb and @mrbdrm thank you very much for your help ... I have to wait wappler team answer..
my last try
SELECT * FROM rooms WHERE id NOT IN {{query1.urun_id.toString()}}
and you can debug the values by setting the result on some element in the page.
so add a response and set the status to 500 and the text to the query value
1 moment … I will try …
@mrbdrm .... YEAP WORKED !!!
Nice 
Thank you very much again …
can you share screen shot for responce usage (debug)
im sure it will be easy to fix. with some debug to the value
and btw
once you set the respond with code 500 you will see it on the browser console. so its faster to debug like that
Well done @mrbdrm, glad it is all working finally.