One Join is fine.
Two Joins I can live with.
Three Joins and we are starting the project from scratch again.

Fuck you! What do I pay you for? Eating berries? No!

Eh?

shit 😁😁😁😁 made my day. big thank you

So, selecting users with photos.path plus roles & permissions joining where in pivot role is too much? :p

@lotd as long as I don't see 3 Joins you can keep your job. I make the rules here kiddo.

@codeStalker good luck keeping people around if your database grows in complexity.

@Ashkin actually am working on a project now, and I purposely work hard towards doing less joins. I use these normalisation stuff i learnt during my masters to accomplish this. But today I had to perform 3 Joins which I thought so much of alternative solutions.

Conc. It's fine but too much joins make things difficult to understand. It's like trying to understand a pointer to pointer to a pointer 😡

Downside too is, you can't avoid joins if your tables are truly normalised.

@codeStalker

It's good practice to limit joins as much as possible -- and subqueries, too. If you do this, your queries will be cleaner, faster, and easier to understand.

However, it's not always possible, especially on complex databases or when everything is nicely separated.

For example: when I was writing a Commercial Invoice PDF generator, I needed to join:
- invoice
- invoice_line_item
- salesorder
- salesorder_line_item
- product
- product_combination
- contact
- contact_address
- contact_name (stupid dba even separated out first,middle,last names...)
- package
- coupon

There was no design/overview doc for the database, so I had to figure all of that out by experimentation. It was ridiculous.
However, it did keep everything nice and separated!

Also, my invoice generator was extremely fast. A four-page invoice took just over one second from request to delivered. and the PDF it builds is really sexy, too (:

@Ashkin "invoice generator was very fast."

I believe that to be true.. I did some reading and realised that most SQL are backed by optimisers that take care of most these things. So if you're join 10 tables and returning one row it doesn't really make a difference.

Databases are interesting and overwhelming to me. I have plans of going through the mySQL source code and probably making some experiments for myself. Will be worth it. Anyway thanks ...

@codeStalker 😊

Materialized views FTW

Much join inside of subquery