How to lower number of db queries?

[tome...@gmail.com]

This might be a noob question, but I am not sure, what is the idiomatic way to do it.

I have two models:
-module(employee, [Id, PersonId, Company...]).
-belongs_to(person).

-module(person, [Id, Name, Bio...]).

I have a controller, that is returning only list of employees:

list('GET', [], _) ->
 Employees = boss_db:find(employee, []).
 {ok, [{employees, Employees}]}.

 and in my template, I use something like this:

{{ employee.company }}
{{ employee.person.name }}
{{ employee.person.bio }}
[...]

I've found, that for every property in person table, I have one query to database for person record, so if person record has 6 properties to display, I have 6 additional queries for each employee.

For now, I just enabled caching. Is it the best way of solving this problem? Or maybe I should firstly get all employees and all people in my controller and then prepare structures for views? This also might be expensive, because for each employee, I would have to iterate over list of people.

Or maybe the best way would be to break the boss_db abstraction and use boss_db:execute directly on Postgres and use JOIN?

[Evgeny M]

I'm not sure if {% with %} tag caches data, try it yourself, it might work or might not:

{% with person=employee.person %}

    {{ person.name }}

    {{ person.bio }}

{% endwith %}

But anyway even if it works you will have a single query for each of employee. I think it's better to get all the persons in one 'in' query, sort them, then zip(Employees, persons) and use this zip in the template. 

At the moment there are no left joins in BossDB, probably because key-value DBs don't support them. I think it is possible to implement joins for relational adapters

and return result as a list of maps or list of proplists, it should be moderately easy to do this bypassing boss caching. But atm you have to do joins yourself.

суббота, 29 ноября 2014 г., 4:09:26 UTC+3 пользователь tome...@gmail.com написал: