Level 29
Can be done with joins, make your self aware, you will find an answer ...
http://www.sitepoint.com/understanding-sql-joins-mysql-database/
Mar 18, 2016
8
Level 7
Query Builder: Joining tables
My database contains information on people. Most of it is stored in the persons table, but there are other tables that contain additional information, e.g. protocols on interviews with these people. This is the simplified example of a typical query:
$query = DB::table("persons")
->leftjoin("protocols", "persons.id", "=", "protocols.personId")
->select ("persons.name", "protocol.text")
->where("protocol.id", ">", 10")
->get();
Let's assume there's a John Doe in my persons table and there's a protocol for him with id 3. The query won't return any of Johns data because 3 < 10.
But I need to get every person. The only thing I want to exclude are the protocols with an id <=10 themselves. In my example, I'd like the query to return John with an empty protocols.text. How can I achieve this?
Thanks Pida
Level 52
There are multiple types of joins. Look into them because a LEFT JOIN will not give you the results you are expecting.
Level 7
I don't understand what kind of JOIN I should use.
- I described why LEFTJOIN doesn't work.
- (INNER) JOIN won't work as there are persons without any protocols.
- RIGHTJOIN won't work as I need to get every person, not every protocol.
Is there something I miss?
Level 29
So now you are familiar with joins :) This can be achieved with Left join and temp table.
I will just write the sql query, convert it to eloquent
select pr.name, pc.text from persons pr left join (select * from protocols where protocol.id > 10) pc on pr.id = pc.personId
Level 56
Here I think you just need a has many relationship between person and protocol. Why do you want to make a join ?
For the join here you have a condition on the protocol table, so only rows with a protocol are returned. You jours use ON :
$query = DB::table("persons")->select("persons.name", "protocols.text")
->leftjoin("protocols", function ($join) {
$join->on("persons.id", "=", "protocols.personId");
$join->on("protocols.id", ">", 10);
});
->get();
But again here you need just a has many relationship
1 like
Level 29
Totally forgot about that :(
Level 7
Thank you both! I'm using several relationships now in my code and I could solve my problem, but only with some addional code after querying the DB.
I wonder if there is an easier way. In this example, is there a way to use the protocols data inside the query? In line 2, I try to set ´protocol_flag´ to 1 (if there are any protocols for this person) or to 0 (if there aren't any). This syntax does not work::`Unknown column 'persons.protocol
$query = Person::with(["protocols"])
->select(DB::raw("(CASE WHEN (persons.protocols) THEN 1 ELSE 0 END) as protocol_flag"));
Is there an alternative syntax?
@pmall, I think you meant this (note 'on' vs. 'where' in line 4)?!?
$query = DB::table("persons")->select("persons.name", "protocols.text")
->leftjoin("protocols", function ($join) {
$join->on("persons.id", "=", "protocols.personId");
$join->where("protocols.id", ">", 10);
});
->get();
Level 56
you should describe your use case, there must be a better way of doing this. For example your case, just use a model accessor.
Please or to participate in this conversation.