Problem with a query... too complex or wrong?

[markoax21]

Hi! I have a quite complex problem (for me) that I don’t know how to solve.
I have two tables.

MGANA contains codart (article code) and price (listino1).
It is linked to LISTCLI by the same codart field (which may or may not exist)

If LISTCLI.codcf has a certain value (I think I’ll pass it by URL, then reading it from a GET), and the product code MGANA.codart also appears in the LISTCLI table (LISTCLI.codart) I have to show the user the LISTCLI.prezzo field (new price) instead of MGANA.listino1

I don’t know how to do this quickly!

I tried to do two queries, basing the second on one of the fields in the first.

so I was thinking of applying a repeat, in which to base a condition that shows a value rather than another on the user’s page.
Read-only and not editable.

Then there is a cart, which I have already made and which works perfectly

I am stuck on the first step, I do not know how to go on and above all I am having the wrong way of dealing with this problem.
Could someone help me out?
Thank you!!!

[htatd]

You’re almost there with your solution.
You just have to put the single query “dati_listcli” inside the repeat, look for LISTCLI.codcf with the id from the repeat, and then the rest as you did it already with conditions.

[markoax21]

I’ll try that.:

But how I can pass value “listino1” in a user form? (the value of “prezzo” text input)

I’ve tried with “variable” in App Component, but I don’t know how to do that (i don’t see it in “Data Bindings”. And maybe it’s not the correct way to achieve this

[guptast]

You can try with a LEFT OUTER JOIN between the two tables in a query. Select dati_mgana table, then select dati_listcli table and change the join type to LEFT OUTER JOIN on field name codart. Under query conditions - add listcli.codecf equal to $_GET.codcli. This will give you a list of prices for fields listino1 and prezzo from both tables including any null values for prezzo.

Then in the user form, use a ternary like this to show prices

{{query.data.prezzo == null ? query.data.listino1 : query.data.prezzo}}

[markoax21]

Thank you for your help
The query works but not completely: I have to see also product in “mgana.codart” who not are present in listcli

In this case, I should see also a codart “ccccc”

[guptast]

Does codart ccccc display in the query result if you remove the where clause?

[markoax21]

No, don’t display

[guptast]

With the LEFT OUTER JOIN, the query will return all rows from mgana table. Try setting up this query in the Database Query Builder instead of the Custom Query, without adding the listcli.codecf condition and check what result the query is returning in the browser dev tools.

[markoax21]

@guptast

{{query.data.prezzo == null ? query.data.listino1 : query.data.prezzo}}

Excuse me, I told you a lie.
If I remove “WHERE” I see all the articles of MGANA table… and also “cccc”

[guptast]

No problems at all. To get all rows from mgana table where codcf is not filtered by $_GET.codcli, under conditions in the Database Query Builder, add a conditional by clicking on the ? and picking $_GET.codcli via the dynamic picker.

[markoax21]

I’m just a few steps away from the solution.
I did a Custom Query that works.
Here is it:

Last problem: in the page I’ve also two text field for filter product (by code and description)

If I change the query with those fields, product display no longer works (but in query test it works)
It is certainly the fault of the “codartfrm” “descfrm” empty fields but I cannot make a standard query because it is too complex!

if I force the default value of the fields with ‘%’, the query works again. So the problem is when those fields are empty!

Any ideas???

[bpj]

When binding to the parameter you could add .default('%') so if empty it should just use the wildcard.

[bpj]

No in the custom query:

use .default(’%’) on the expression

e.g.

{{$_GET.codartfrm.default('%')}}

[markoax21]

OK thanks. There is a final problem. In this way the search functions works only if I type the words in full.
For example, for a product called “article 1”, it is not displayed to me until I write the complete name. It is not enough just to “artic” as it should be

[bpj]

OK, you need a wildcard each side then:

{{'%'+$_GET.codartfrm+'%'}}

In this case, as the wildcards are already present, you don’t need the default formatter

[markoax21]

Yes!!! Thank you very much