New db manager subtables - can't create the join I want?

[karh]

I feel like the database manager isn’t recognising my tables properly - or I’m doing it wrong.

Please help!

I want to do a simple query, that has a few joins.

This is the working raw query (using Beekeeper studio as db manager):

SELECT ccc.course_id AS course_id, cccl.course_chapter_id AS chapter_id, cl.id AS lecture_id 
FROM courses_lectures cl
JOIN courses_chapters_courses_lectures cccl ON (cccl.course_lecture_id = cl.id)
JOIN courses_chapters_courses ccc ON (ccc.course_chapter_id = cccl.course_chapter_id)

This is the output:

Now trying to recreate this in wappler:

This is the way my tables are connected:

1. courses contains courses_chapters contains courses_lectures.

2. courses is connected with a ‘join’ table to courses_chapters, the name of this ‘join table’ is courses_chapters_courses.

3. courses_chapters is connected with a ‘join’ table to courses_lectures, the name of this ‘join table’ is courses_chapters_courses_lectures.

This is how I am trying to get to this data using the query builder:
Attempt 1. bottom up

1. Add courses_lectures
   
   
2. Try to join with courses_chapters_courses_lectures
   
   
3. Can’t… join, that table is not available to pick.

Attempt 2. top down

1. Add courses:
   

   

2. Add courses_chapters_courses to join with
   

   
   
   sidenote: it didn’t automatically do the correct join here

3. Add courses_chaptres to join with
   

   

sidenote: it DID automatically join correctly here

4. Try to join with courses_chapters_courses_lectures:
   

   

5. Can’t… join, that table is not available to pick.

Any ideas?

[George]

You don’t need to do any joins to the sub tables - just add them to the columns list for output and maybe select their detailed columns by double clicking to go the the detail of the sub query.

This way you will get a nested structure of the main data plus the sub tables as you wish.

If you don’t need that and really want to go with joins you should start from the last table and then join with the parents.

[karh]

@George

1. I'm trying to do this the way that you showed, after I asked this in the meetup call.
   This seems to generate a faulty query:

"status": "500",
  "code": "ER_NONUNIQ_TABLE",

I'll send the video in a DM (rather not show my entire database logic publicly).

2. If I do want to do the joins, I am stuck here:

@karh

This is how I am trying to get to this data using the query builder:
Attempt 1. bottom up

1. Add courses_lectures

2. Try to join with courses_chapters_courses_lectures

3. Can’t… join, that table is not available to pick.

[karh]

My solution (thanks to help from George):

To do it with the UI:

1. Select the parent table (for me courses) and add the multi ref column:
   

   

2. Double click on that column
   

3. 

4. Double click the column again

5. Do the same as step 3

6. In case you get ‘not unique alias’ errors, make sure the alias in the join is different:
   

   

To do it with joins how I wanted to do it:
No solution (yet?). As far as I can see I can’t select the tables I need to join with.

[Teodor]

Fixed in Wappler 5.0.4