How do I repeat in AC with two types of content inside?

[karh]

I’m a bit confused.

My goal is to show a list of cards, each card is a course. Some courses are owned, some are not:

Right now the card you see are all owned courses.

I retrieve them with a query that joins 3 tables:
Users → users_enrolled_courses → Courses

The users_enrolled_courses contains a reference to course_id and to users_id. So if there’s a record in there combining user John with course A - then John owns course A.

How do I achieve this goal with the repeat on the page?
I think I need to feed the repeat expression an array of all courses, which has an extra field on if it’s owned or not?

*Edit: I tried with the new manipulations in server connect - but that only gives me the owned courses again

*

[sid]

Your query needs to run on courses table & do a left join with enrolled courses.

[karh]

Ah that makes sense. That would give me the owned courses + the other courses.

And then how do I display them on the page in a different way?
How can I get an “if course == owned, then show ‘buy button’, else show ‘watch button’”

[sid]

Something like this should work:

<button dmx-show="enrolled_course_id">Watch</button>
<button dmx-show="!enrolled_course_id">Buy</button>

Make sure course_id from enrolled table is aliased as enrolled_course_id or something like that.

[karh]

Thanks so much for helping Sid. I’m still missing a piece.
I’ve spent the last 2 hours trying to set up the left join.

Here is the query:

SELECT *
FROM courses
LEFT JOIN users_enrolled_courses uec ON (uec.course_id = courses.id)

Using DBeaver to see the results in a table instead of the json output from wappler:

Any idea what I’m doing wrong?

In the end I want to have an array that contains ALL courses and that shows if the user is enrolled in that course or not.

[sid]

Do another left join with USERS.
Then, add condition - user.id is null or user.id = logged.in.users.id.

[sid]

@karh

I’ve spent the last 2 hours trying to set up the left join.

That's good. Sounds like you now know how its working. This is time invested.
I would recommend to take a small SQL course if you are new to this.. will help a lot when your app scales, you need complex reporting and complex features and you start to build custom queries in the server actions.

[karh]

Haha thanks, you’re very right. Slowly becoming a full fledged programmer last few weeks… been learning a lot about node, express and now SQL. Only downside is that the development isn’t going as fast as I wanted it to go… but it should be an exponential curve.

Going to try out doing 2 left joins now as you suggested!

[karh]

Getting close…

user_id ‘37’ will be replaced with current logged in user id.

I now see all the courses that user owns. Including 1 course that he doesn’t own. With user_id as NULL.

But I’m missing the other 4 courses that he doesn’t own.
Not sure why…

[sid]

Probably because those course are owned by someone else… So there is no row which has user id as null for them.

Looks like you might need to create custom query for this. The solutions I suggested so far could have been created via query builder, but not this one…

The query you have in dbeaver, add condition user_id = 37 in the join itself. And remove conditiin from below.
See if that works.

[karh]

Looks like it works…

Tbh not 100% sure why yet.

Thank you!!

[sid]

You should put the OR condition in brackets for sanity.
So how it would read is join on (courses.id = course_id AND (user_id = 22 OR user_id is NULL)).