logo
down
shadow

How to join and filter between 3 tables with ecto query expressions?


How to join and filter between 3 tables with ecto query expressions?

Content Index :

How to join and filter between 3 tables with ecto query expressions?
Tag : postgresql , By : Angel Paunchev
Date : January 11 2021, 03:34 PM

it fixes the issue I don't think a left join is necessary for this scenario. You should be able to inner join on all of the tables because an inner join will indicate the presence of an assistant and rule out any possibility of getting an author without an assistant. You should be able to write something like this:
query = from(p in Post,
  join: a in Author,
  on: a.id == p.author_id,
  join: ast in Assistant,
  on: ast.author_id == a.id,
  # See notes below about the distinct
  distinct: p.id
)

Repo.all(query)
create unique_index("assistants", :author_id)

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Join two tables belong to two database in Elixir Ecto


Tag : elixir , By : woxorz
Date : March 29 2020, 07:55 AM
This might help you Your code is perfect. This is a bug in Ecto. I have fixed it in master if you want to give it a try. :)

How to create conditional join in Ecto Query?


Tag : elixir , By : kakashi_
Date : March 29 2020, 07:55 AM
Hope this helps This issue on Ecto may be relevant for you.
Currently you have two options:
query = from u in User, 
        join: a in assoc(u, :api_keys), 
        where: u.email == ^email,
        where: a.is_active == true,
        select: {u, a}
user = Repo.one(query) |> Enum.map(fn ({u, a}) -> %{u | api_keys: a} end)
api_key_query = from a in ApiKey, where: a.is_active == true
query = from u in User, 
        join: a in assoc(u, :api_keys), 
        where: u.email == ^email,
        preload: [api_keys: ^api_key_query]
user = Repo.one(query)

Join 2 Tables And Filter Sql Query


Tag : sql , By : Fahad
Date : March 29 2020, 07:55 AM
this one helps. I have Two tables I need to total occupancy from the two tables , Assuming Data Type of ReservedDate is Date.
Try:
declare @startDate date
declare @endDate date

select @startDate = '2016-12-28'
select @endDate = '2016-12-31'

-- Recursive CTE to generate dates between start and end dates 
;with dateRange as
(
  select @startDate as dt  
  union all
  select dateadd(dd, 1, dt)
  from dateRange
  where dateadd(dd, 1, dt) < +dateadd(dd, 1, @endDate)
)
select  
  tbl_master.dt as ReservedDate,
  tbl_master.TableType,
  tbl_master.Shared,
  case tbl_master.shared 
    when 'FULL' then 
        tbl_master.avail+coalesce(tbl_shared.shared_count,0)
    when 'SHARED' then
       tbl_master.avail-coalesce(tbl_shared.shared_count,0)
    else 
       tbl_master.avail
   end as Totalavail

from (
  -- Join date range data with `TableMaster` to get table availablity
  select dateRange.dt,TableMaster.TableType,TableMaster.Shared,count(*) as avail
  from dateRange left join TableMaster on 1=1
  group by dateRange.dt,TableMaster.TableType,TableMaster.Shared
) as tbl_master

-- Join `TableSharedDetails` for adding and subtracting counts from table availability   
left join (
  select ReservedDate,TableType,count(*) as shared_count 
  from TableSharedDetails
  group by ReservedDate,TableType
) tbl_shared on 
tbl_master.dt=tbl_shared.ReservedDate
and tbl_master.TableType=tbl_shared.TableType;

Join 3 tables using ecto query


Tag : sql , By : jan
Date : March 29 2020, 07:55 AM
like below fixes the issue Here is a scenario with Postgres , You just need to write 2 joins:
from hearing in "hearings",
  join: hcl in "hearing_category_link", on: hcl.hearing_id == hearing.id,
  join: category in "categories", on: hcl.category_id == category.id

How to translate this SQL join statement into an Ecto query?


Tag : elixir , By : micate
Date : March 29 2020, 07:55 AM
hope this fix your issue After some digging, I discovered I was able to do this using pure Ecto syntax using a subquery in a join, like this:
user_id = 2

subquery = from(s in Message,
                select: %{ sender_id: s.sender_id, max_time: max(s.inserted_at) },
                where: s.receiver_id == ^user_id,
                group_by: s.sender_id
               )

query = from(m in Message,
             join: s in subquery(subquery), on: m.inserted_at == s.max_time and m.sender_id == s.sender_id,
             where: m.receiver_id == ^user_id
            )

Repo.all(query)
 > Ecto.Adapters.SQL.to_sql(:all, Repo, query)
 {"SELECT m0.\"id\", m0.\"sender_id\", m0.\"receiver_id\", m0.\"body\", m0.\"received_at\", m0.\"inserted_at\" FROM \"messages\" AS m0 INNER JOIN (SELECT m0.\"sender_id\" AS \"sender_id\", max(m0.\"inserted_at\") AS \"max_time\" FROM \"messages\" AS m0 WHERE (m0.\"receiver_id\" = $1) GROUP BY m0.\"sender_id\") AS s1 ON (m0.\"inserted_at\" = s1.\"max_time\") AND (m0.\"sender_id\" = s1.\"sender_id\") WHERE (m0.\"receiver_id\" = $2)", [30064, 30064]}
Related Posts Related QUESTIONS :
  • pgadmin can't log in after update
  • what is the postgresql query to find the timestamp without time zone
  • Port-foward command to access postgres from outside AKS not working in kubectl due to ampersand
  • Combine rows in postgres
  • How to find data length and index length of particular tables in a postgresql schema?
  • Shell Script To Write PostgreSQL SQL Query Results In To CSV Format
  • DOCKER environment in production
  • TimescaleDB query to select rows where column value changed from previous row
  • Temporary Postgres table gets lost prematurely
  • Partial unique index in postgres in relation to other rows
  • API returns only last row for a nested struct
  • Converting PostGIS table (postgresql) to GeoJSON
  • How to find if the row is part of index?
  • pgAdmin argument formats can't be mixed
  • How to calculate subtotal in postgreSQL
  • Restoring PostgreSQL database without having a dump just the database files
  • Best way to track the progress of a long-running function (from outside) - PostgreSQL 11?
  • Add columns but keep a specific id
  • pg_restore: [archiver] unsupported version (1.13) in file header
  • How to DB query and then check the result
  • Select multiple rows with condition on each columns
  • Postgres date format with Greek day inside
  • Firing Postgres triggers on different table columns
  • Prepared transactions with Postgres 8.4.3 on CentOS
  • OpenStreetMap and Hadoop
  • OSGI & Apache Commons-DBCP Classloading Issue
  • Postgresql full text search in postgresql - japanese, chinese, arabic
  • Strange postgresql behavior
  • PostgreSql XML Text search
  • Can't install psycopg2 (Python2.6 Ubuntu9.10 PostgreSQL8.4.2)
  • Enable encryption on existing database - AWS RDS Postgresql
  • Tagging DB with formatted SQL + XML
  • Query from a variable table in postgres
  • Returning record ID's from PostgreSQL using PetaPoco
  • Why am I getting an error that I cannot concat two different datatypes even after casting the fields datatype
  • Writing a rather obtuse JSON query using Slick
  • How to set a dynamic "where" in a view based on the user querying data?
  • K8S use volume to keep DB data
  • Merging an array of JSON objects to one JSON column in Postgres
  • Update values of a row to the max of old and new value
  • I want to get the book that has been issued the maximum no. of times
  • Pg-promise client doesn't retrieve special characters properly from ElephantSQL cloud database
  • pg_dump/pg_restore lc_collate English_United States.1252 Windows Linux migration
  • Set default value from a query result
  • Function not found in PostgreSQL
  • How to get postgresql_query results from Ansible
  • pg_dump to copy schema to remote server
  • PostgreSQL - export output to CSV
  • How to sum/total counts by group?
  • Postgres Check availabilty overlapping dates
  • psql copy from csv automatically generating ids
  • execute a postgresql query from a file and write the output to another csv file
  • Invoke Stored Procedure and Return ID
  • Converting PostgreSQL interval to seconds produces wrong values
  • Aggregating columns and getting count of values as row
  • Find overlapping date ranges from two tables?
  • Encoding UTF-8 problem while JPA/hibernate execute sql-script with Intelli-J
  • In PostgreSQL, which types can be cast with the type name first?
  • What is the relation between the size of the DB and the size of /var/lib/postgresql/data
  • What is difference between BIGSERIAL and SERIAL
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com