How to get first x elements from the list?

... |> Enum.take(3)

I just found out about Enum.slice which can help me with that…

query |> Enum.filter(fn x -> x.created_by == "user" end) |> Enum.slice(0..2)

Enum.take() as @NeutronStein suggested is even nicer solution.

So this is the answer to my first question… if anyone can share their thoughts about getting and filtering data this way compared to the db way, I would be very grateful.

query |> where([x], x.created_by == ^"user") |> order_by([x], x.id) |> limit(3)

If you are not using rest of the rows - may be you can add limit directly to the query ?

Generally a single DB level query will always will be faster than writing two queries - you have to consider the overhead of serialisation. You can check query plans and then add indexes if needed.

I don’t have any right to ask what I’m about to ask because I don’t know really anything about what you’re actually doing… but I’m not going to let that stop me.

Why not do the filtering in database query? It seems to me that you’re going to incur unnecessary latency transferring data between the database and the application; especially if they’re different servers and the data has to cross a network; and you’ll be processing the data (filtering) in a less efficient manner given that database servers are carefully designed to perform well with that workload. There are other reasons to do as much data processing in the database during retrieval but they kinda fall into the category of dealing with more data than necessary across the steps of the process.

Yes, that’s the query I have in place but was thinking about changing it to a bigger query filtered by app.

I could, in fact, I’ve built it this way but now I’m thinking that having 2 queries would be slower then 1 bigger query filtered by the app. As I said, I may be wrong and I want to learn and understand those things better and that’s why I’m asking.

I don’t see how this follows. If you can write 1 query for the database to get unfiltered data, why do you think you need 2 queries to achieve the desired filtering at the database?

I’m sorry for not making it clear enough…

I have users and each user can be a project’s creator or contributor. In a user’s account page, I want to list projects where user is a creator but also list projects where user is a contributor.
So, is it better to create 1 query with all user projects(both creator and contributor) and then on an app level, filter them and show them in different groups or is it better to have 2 separate queries, 1 for projects where user is a creator and 1 for projects where user is a contributor?

That’s what I would do. Get both kinds of users with one DB query and then programmatically split the list into two – author / contributor. Super easy when you add Enum.group_by at the end of your pipe.

I would need to see the actual table structures to give you the best advice. Having said that, based on my understanding of your description, I’ve heard nothing that would require you to use two queries to get the complete filtered (and even sorted) data from the database in a single query.

Since you’re getting the unfiltered data via a single query, it sounds like there is a single row description that works for both the creator and contributor data which can be a sticking point in these cases. This could be within the realm of a simple join query with appropriate where predicates or a union query depending on the details of the information architecture you’re dealing with.

So for example. I have a very enterprisy application which has simple firewall like rules allowing a tenant to specify which hosts or networks their users can be seen to be connecting from. There is a table for global rules which apply to all tenants, there is a table for the rules of each tenant, and then a single tenant can have multiple application instances each of which can define their own rules which is kept in its own table… and if no applicable rules are found there’s a default rule to apply. When a user wants to authenticate, I need to find which, if any, of the global, tenant, or instance network rules matches the host we see the user originating from (and yes, there are many problems with this sort of thing in practice… but, let’s stick to databases for now). So I query the database in a single database query which queries all three tables filtered for the user’s tenant, target instance, and originating host, sorts the rules of all three tables in order of precedence, and returns the single row which will be applied for the specific scenario given the variables of the query or returns the default rule if no records match the criteria. In this case I do this with a union query since I can order based on the precedence of the tables compared to each other. The application only ever sees the single record which is the rule that governs that particular request.

Anyway, I say all that to demonstrate that mixed data queries can be made into a single query which can filter down to the precise records the application needs. The biggest obstacle is if the shape of the returned records can’t be sensibly reconciled, but it sounds like you’ve achieved that already.

addendum

Finally to be clear, I’m speaking about the filtering scenario:

Enum.filter(fn x -> x.created_by == "user" end) |> Enum.take(3)

What @dimitarvp describes is related, but a little bit different and I don’t necessarily take issue with what he’s saying. But bringing rows from the database to the application to just decide what rows to discard is what I would suggest you reconsider.