获取id的标签

Question

What is the best way to get labels of id's .

Here is the problem i'm facing .

I have a many tables that only contains id's (subject_id , level_id , place_id , etc..)

What is the best way to display the labels of those id's without making a complex sql query when displaying (have minimum of 6'ids) ?

The other options which is not very nice to do would be to call get_label(id,table,lang)

but of course you can see the problem for each column (Total queries = column * rows)

Any better solution or i'm stuck without doing the join on 6 tables ?

If it's helps i'm using kohana

here is what i have ...

and the subject table for the subject_id :

I have for every field_id a table that correspond .

In term of performance which is better making a join or just calling a query to get the specific label when needed . ?

Answer 1

You want to use a SQL JOIN for this.

SELECT t1.*, t2.subject_en, ...
FROM table1 t1
JOIN table2 t2 ON (t2.id = t1.subject_id)

A JOIN has a much better performane - you have only a single query which can be properly optimized by the database engine while doing a SELECT while iterating over the rows from the initial query would give you n+1 separate queries.