Create this Query in Wappler

Wappler General How To
#21

This is exactly what I tried.

  1. Fetch IDs to exclude from second table.
  2. Fetch all row from main table with condition where id not in {{??}}

Could not figure out how to use the response of first query as param in second.

I even created a view that would return IDs as 1,5,3,6 in a single row single column.
But could not get it to work in the condition filter.

#22

I think you are correct with the <>, also you can use distinct to filter out duplicates

SELECT DISTINCT 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}}

@TomD I was also first thinking about making 2 queries, but then I noticed that we are currently missing a Formatter in Server Connect to get the IDs from the first query as an array for the second query. We have the formatter in App Connect but not in Server Connect {{ query1.data.values('code_value_id') }}.

Problem is that a query result looks like:

[{ id: 1 }, { id: 2 }, { id: 3 }]

and the IN operator wants only the values for the id:

[1, 2, 3]

and the values formatter of App Connect exactly extracts that, the values formatter currently available in Server Connect is to extract the values from an object, not from an array with objects.

#23

Using the ‘keys’ instead of ‘values’, it works (thought not producing any useful results of course). You can just use the variable in the NOT IN part and the query produces:
WHERE (id NOT IN (0, 1, 2, 3, 4, 5, 6, 7)

#24

What is the ‘values’ function for? I assumed this might be the answer (thought I’ve never used it before). It would certainly be useful to have a formatter as you describe.

#25

@patrick, spent several hours on this yesterday, various join options without success (love a challenge)

Main problem is that there is not always a join to the secondary table but sometimes there are multiple joins
In the case of a multiple join the “AND tbl_code_value_exclusion.campaign_id = {{3}}” removed that entry but not any other entry that the join may have selected via that ID so the ID still gets selected in the output

1 Like
#26

I am not sure I follow. Which keys/values are you referring to?

How to set the value returned from query into a variable? Or am I misunderstanding this too?
Because if this can be done, using variable in NOT IN would probably solve this.

#27

Interesting

so initially execute
"select id from tbl_code_value_exclusion where tbl_code_value_exclusion.campaign_id={{3}}"

Turn that output into an array and assign it to a variable, say “excludelist”
The use the array as a parameter for the initial query
SELECT * FROM tbl_code_value where (code_id={{30}} or code_id={{31}}) and is_active=1
and id not in {{excludelist}}

May have to try that, i like the thinking

#28

How to do this part?

#29

Core Actions => Set value

#30

These options are in the Data formats panel. Here I’ve assigned the results of the first query to a variable and want to format that variable so the IDs are put into an array for the next query:

image
image.png1052×667 53.1 KB

This works if I use the Keys option - ie an array is created which can be used in the query - but not if Values is used (which should return the values you actually need).

#31

Oh. Got it now.
So as Patrik said, support for values is just on client side as of now. :slightly_frowning_face:

I think I did try this at some point yesterday. :sweat_smile:

#32

No NishKarsh, that formatter is available at server connect end when using a set variable via server core actions

#33

Yes, so in Wappler, it would look like this:

image

However, this results in an error. That’s when I experimented with ‘keys’ instead of ‘values’ - no errors and the query was valid and excluded the array of IDs (except they were keys rather than the required values).

#34

Yes, the formatter is present, but it doesn’t work.

#35

Yes, horrible feeling the exclude array will have to be built manually via a repeat

#36

But just temporarily hopefully - assuming it should be possible now if that’s what the ‘values’ function should produce.

#37

Tried that too. But failed to create an array. This will also be done by Set Value?

#38

Yes, also having an issue working out the convert query to array part other wise this could be a solution, perhaps @patrick could help?

#39

Do you use PHP?

Edit the action file directly, use the following json for the action step:

{
  "name": "query1",
  "module": "dbupdater",
  "action": "execute",
  "connection": "connection1",
  "query": "SELECT * FROM tbl_code_value where (code_id=? or code_id=?) and is_active=1 and id not in (SELECT code_value_id FROM tbl_code_value_exclusion where tbl_code_value_exclusion.campaign_id=?)",
  "params": ["{{30}}", "{{31}}", "{{3}}"]
}
#40

This project is in IIS. Would the same thing work there?

Also, when you write {{30}} it means $_GET.somevar or {{$_GET.somevar}}, or neither?