How do I count referenced records? (Mysql)

Wappler General Need Help
1

Goal: I want a amount_of_lectures column in my courses table, which counts all lectures that reference this course.

This is what I want and how the db model looks like:

image
image842×667 105 KB

I was thinking of doing the count in the query - but then I add a bunch of unnecessary load I think. This query is executed often, and the amount_of_lectures will change perhaps once a year or so.

My best idea is to make a MySQL expression that counts, but I have no idea where to start and if that is the best approach.

Any suggestions on how to approach this?

2

In Wappler you could create a query which just gets the number of lectures and only call it when you need it.

3

Hmm in that case it’ll be in the same query that I have right now to get the courses in the first place, which is done several times in a user session. That seems highly inefficient, no?

4

If the data rarely changes the database will likely cache this for you so maybe not a big deal.

If that doesn’t satisfy you can get the value from a server connect element and cache to local or session storage and set the duration to whatever you’d like.

5

Oh didn’t know the DB will do it for me, that’s nice!

I have this working solution:

  1. Made column that holds the count of lectures
  2. Make it update every time a lecture is added/removed through a server action. Getting the count through this query: SELECT count(*) AS count FROM courses_chapters_courses_lectures cccl JOIN courses_chapters_courses ccc ON ccc.course_chapter_id = cccl.course_chapter_id WHERE ccc.course_id = :P1
    Then running an update query with that result
6

No - I meant run a seperate query and either cache the results or just run the query when you need it if the data is only needed periodically.

However, if you’re talking about counting just hundreds of records a couple times a session I personally wouldn’t worry about the server load as it’s not a complex query. YMMV.