Create this Query in Wappler

Wappler General How To
#1

Hi.
I understand that Wappler does not support sub-queries as of now. And the best option in these cases is to create a view, and use that.
But, right now, I find myself with a query that cannot be converted to view.

SELECT * FROM tbl_code_value where (code_id={{30}} or code_id={{31}}) and is_active=1
and id not in (SELECT code_value_id FROM tbl_code_value_exclusion where tbl_code_value_exclusion.campaign_id={{3}})

tbl_code_value contain multiple rows for each code_id with id as primary key.
tbl_code_value_exclusion contains list of ids which should be removed from result set.
Values in {{}} are dynamic - being sent to server connect via GET.

Can anyone suggest how/if this can be done in Wappler?

#2

Havn’t actually tested this but off the top of my head I think this may help

Assuming campaign_id is a foreign key of key field code_id I think the following query will return what you want

If you left join the two tables you will return all values in the tbl_code_value and the corresponding values from tbl_code_value_exclusion where there is a match.
If no match then campaign_id will be set to Null

You can then filter the results on campaign_id<>3

I think the query you need is:

SELECT * FROM tbl_code_value
LEFT JOIN tbl_code_value_exclusion on code_id = campaign_id
where
(code_id={{30}} or code_id={{31}})
and is_active=1
and campaign_id<>{{3}}

If it is not exactly correct you should be able to tweak it accordingly

If you have problems check the output of the query without the final and campaign_id<>{{3}} then add the filter after you are happy with the main part.

1 Like
#3

Thanks for the reply @Hyperbytes
I should have mentioned, campaign_id does not relate the two tables.
They are related by id from main table & code_value_id in second table (one to many).
campaign_id is a separate parameter.

#4

As long as you have a common key between the two tables to make the join, the technique should work
i.e.left join then filter

Only type of query have needed which I have not been able to replicate with some sort of join is a UNION

#5

I can relate the two tables using join, but I need to join them in a negating condition - not in.
Any ideas for that?

#6

No you dont need the “not in”.

If you left join two tables you get ALL records from the left (main) table and either the corresponding values from the right table. Where no match the right table is set to null values

so if you have a table

ID: 1 Name: John
ID: 2 Name: Fred
ID: 3 Name: Bill

and you left join with a table

ID: 1 Lastname: Smith Age: 55
ID: 3 Lastname Jones Age: 66

A join will return

1,John, Smith, 55
2, Fred, Null, Null
3, Bill, Jones, 66

You can then filter this with a where clause, for example where age <> 55 to remove those unwanted entries

1 Like
#7

The left join table has one to many relation. So I get
1, John, Smith, 22
1, John, Doe, 23
1, John, Good, 32
2, …

Consider 4th parameter in my case as campaign_id. So now I need to apply a filter such that if 24 is not present in left joined table for id 1, I should get John.

#8

So effectively 1 entry in the primary (tbl_code_value) table can have multiple matches in the secondary ( tbl_code_value_exclusion) table as a one to many relationship?
That complicates things somewhat, need to think on that one.
I assume that in some cases there will be no match in the one to many relationship?

1 Like
#9

Yes. And I need all cases where there are no matches as the result.

#10

Yes, nishkarsh, i am starting to think this cant be done with a basic join and is outside the scope of Wappler at present

I think Wappler is in need two server connect enhancements to cover all eventualities

  1. UNION - The ability to merge two sets of query results into a single result
  2. IN / NOT IN - The ability to intersect two sets of query results
    The latter could probably be done in most cases with repeats and conditionals but it is a very messy approach to a basic SQL feature
1 Like
#11

Maybe @patrick can advise here :slight_smile:

1 Like
#12

Any advice appreciated thanks

#13

Indeed, an enhancement is needed. With the other growing capabilities, this now seems like an important part to consider for an upgrade.

Repeat is a good option, but I have not found it to be as easy as other server action components. :sweat_smile: Usually get confused with the output and steps.

#14

Can be quite slow compared with a proper sql query also

#15

Won’t it work with the join?

SELECT tbl_code_value.*
FROM tbl_code_value
LEFT JOIN tbl_code_value_exclusion
  ON tbl_code_value.id = tbl_code_value_exclusion.code_value_id
WHERE (tbl_code_value.code_id={{30}} OR tbl_code_value.code_id={{31}})
  AND tbl_code_value.is_active=1
  AND tbl_code_value_exclusion.campaign_id = {{3}}
  AND tbl_code_value_exclusion.code_value_id IS NULL
#16

I need the values from second table which do not have this campaign_id. So <> {{3}} should work.
But when using join, its a one-to-many relation, so returned values are duplicated, and necessary values are skipped.

#17

No the tbl_code_value_exclusion.campaign_id = {{3}} is correct, the extra tbl_code_value_exclusion.code_value_id IS NULL will remove all the exclusions.

#18

Hmm, maybe I have something wrong, need to test it again.

#19

I was thinking this could be done by splitting the query into two. The sub-query would be run first and the results (list of IDs) could be assigned to a variable. The second query would use this variable in the NOT IN part of the query. However, it doesn’t seem to work, but I’m not sure why not.

I thought using the ‘values’ function would create an array of the ids, but it returns an error (‘array to string conversion’). If I use the ‘keys’ function - eg {{qry_exclude.keys(",")}} - it gives the expected result, but not if ‘values’ is used instead of keys. Is there a way of putting the values into an array so they could be used in the query?

#20

code_value is not to be filtered in second table. Just campaign_id.