Query based on correlativity condition

Wappler General How To
#1

Apologies for the bad english.
One day I saw a question like this but can’t find it anymore:

I have 2 tables.

  1. Class
  2. Aproved_class

Lets say:
In Class table this records:

“id”: 1,
    “name”: “Maths”,
    “correlativity”: 1,
"id": 2,
    "name": "Science",
    "correlativity": 1,2,
"id": 3,
    "name": "Economics",
    "correlativity": 1,2,3

In aproved_class:

“id”: 1,
    “user_id”: “1”,
    “aproved_class”: 1
"id": 2,
    "user_id": "1",
    "aproved_class": 2

So, first i make a query with the user and other with the aproved class, which returns:
Query of user results: {"aproved":[{"aproved_class":"1"},{"aproved_class":"2"}]

What I need is simply, receive the id of correlativity based on the list of the first query.
Lets say:
[{“aproved_class”: 1}] -> Get maths (correlativity 1)
[{“aproved_class”: 1},{“aproved_class”: 2}] -> Get maths and science (correlativity 1,2)
[{“aproved_class”: 1},{“aproved_class”: 2},{“aproved_class”: 3] -> Get maths, science and physics (correlativity 1,2,3)

I tried everything since a lot of time, even spliting the “correlativity” field and no luck.

#2

Are you using the flatten formatter? I think you need to use that to make the 1st query an array, so that you can then use IN

The flatten formatter will convert an array of objects to an array of integers (in this case)

I’ve never used IN before, I could be wrong

#3

@Apple hey!

Thanks for the reply.

Still having troubles:

image
image427×532 5.38 KB

As you can see query1 returns id 5 which is not IN the array

#4

Hi!

Can you show screenshots of your database steps? It would be easier to understand

#5

First table:

image
image1637×310 20.2 KB

image
image1647×165 8.91 KB

Second table:

image
image1640×301 22.8 KB

image
image1642×199 9.51 KB

#6

Sorry, I’m unable to understand and lack the time right now to figure out a solution :frowning:

Hope someone can help you

#7

Thanks, and i think i have to use a custom query, but can find the correct expression.

Found this but it doesn’t work.

#8

You need to turn correlativity into an array to use the IN condition.
You can do this with the explode function I.e.
IN correlativity.explode(",") (check syntax, commenting from memory, not near computer)

#9

Hi Brian!

Even using an arary won’t work. Because the IN operator, is true, but not for my case:
When i have
image

The query using IN (if my students only has aproved the class 1) will retrieve the ID3, ID4, ID5, because the argument is ok (1 is in various correlativity fields).
And my student maybe doesn’t have aproved the class number 2 (wich needs the ID4) or the class number 3 (wich needs the ID5)

Better explain:

This study center, needs to check if the student has previously aproved the class and return the next class, so the correlativity is an important thing here.

image
image

[{“aproved_class”: 1}] -> Get ID3 (correlativity 1)
[{“aproved_class”: 1},{“aproved_class”: 2}] -> Get ID3 and ID4 (correlativity 1,2)
[{“aproved_class”: 1},{“aproved_class”: 2},{“aproved_class”: 3] -> Get ID3, ID4 and ID 5(correlativity 1,2,3)

Join tables also won’t work, the argument is a little more complicated than my skills.

#10

I am thinking a multi join would be more effective and easier to manage.
1 table with student
1 table with classes
1 Multi join with which student has been in which class.
Comma separated lists are a pet hate of mine, in my opinion they have no place in the world of relational databases.

1 Like