Level 73
You are telling it to look for job_offer_uuid on the users table and the users table doesn't have that column.
Nov 30, 2021
11
Level 1
How to access to user data through intermediate table (Relationships)
For a job posting application, I have three tables, which shortly are defined as:
-
users: Just laravel normal users table id as primary key
-
job_offers with field uuid as primary key with field user_id as external key
-
applications id as primary key job_offer_uuid as external key
Because I need to notify job_offer owner any time that application is registered, I'm trying to create a hasOneThrough relationship from applications to users, but without success for the moment.
Based on my understanding eloquent documentation (https://laravel.com/docs/8.x/eloquent-relationships#has-one-through), my actual code in Application model is:
public function publisher()
{
return $this->hasOneThrough(JobOffer::class, User::class, 'job_offer_uuid', 'user_id'');
}
But it fires an SQL error
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.job_offer_uuid' in 'field list' (SQL: select `job_offers`.*, `users`.`job_offer_uuid` as `laravel_through_key` from `job_offers` inner join `users` on `users`.`id` = `job_offers`.`user_id` where `users`.`job_offer_uuid` in (1))
I can get results using pure SQL with a sentence like this (not optimized yet):
select * from `applications` inner join job_offers on `applications`.`job_offer_uuid` = `job_offers`.`uuid` join users on job_offers.user_id = users.id where `applications`.`job_offer_uuid` = '8bd1327a-8079-41e6-9fbe-63da9dd74c1f'
Any clarification should be truly appreciate. Regards!
Level 1
@Tray2 Thank you, I understand that, but I'm not finding the way to express my "has-one-through" relationship.
Level 102
It's a bit confusing as you are listing your models in reverse order. As I understand you, this is the order your wish to call them
- applications id as primary key job_offer_uuid as external key
- job_offers with field uuid as primary key with field user_id as external key
- users: Just laravel normal users table id as primary key
And the class you wish to end on comes first
return $this->hasOneThrough(User::class, JobOffer::class, 'job_offer_uuid', 'user_id'');
Level 1
@Sinnbeck I agree that I don't have a clear view about this kind of relationships on laravel, that is my problem. To clarify the situation: I need to reach User model, starting on Application model, through JobOffer model.
Actually, your statement returns the following sql error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'job_offers.job_offer_uuid' in 'field list' (SQL: select `users`.*, `job_offers`.`job_offer_uuid` as `laravel_through_key` from `users` inner join `job_offers` on `job_offers`.`id` = `users`.`user_id` where `job_offers`.`job_offer_uuid` in (1))
Level 73
@faridsilva Shouldn't that be a has many through relation instead?
https://laravel.com/docs/8.x/eloquent-relationships#has-many-through
Level 1
@Tray2 I'm trying that option precisely, but reading carefully eloquent documentation, it seems that my users table should have a foreign key for the intermediate table and that don't have any sense on my table structure.
I'm really confused because any tutorial or video I found is showing that.
Level 73
@faridsilva Many users can apply to a job_offer right? users can apply to many job offers right?
That makes it a many to many relation and a many to many only has foreign keys in the pivot table.
Level 1
@Tray2
Hi , table users in this case only hosts publishers. So a job_offer record only can belongs to one user. Applicants are stored in applicants, table.
In fact my pure SQL works really fine, but I wish to understand in deep how relationships works in laravel.
->join('job_offers', 'applications.job_offer_uuid', '=', 'job_offers.uuid')
->join('users', 'users.id', '=', 'job_offers.user_id')
->select('users.name', 'users.email')
->where('applications.id', $model->id)
->first();```
Level 73
@faridsilva In my opinion complex queries should be written in SQL and not the query builder or eloquent since they have a tendency to become quite unwieldly.
1 like
Level 1
@Tray2 Ahhhh, thank you for that wise advise. I will keep it mind. Regards!
Level 73
@faridsilva Imagine trying to write this kind of queries with the Query builder or eloquent
SELECT
(SELECT GROUP_CONCAT(a.id ORDER BY a.id SEPARATOR ',')
FROM authors a, author_books ab
WHERE a.id = ab.author_id
AND ab.book_id = b.id) author_id,
(SELECT GROUP_CONCAT(CONCAT(a.last_name, ', ', a.first_name)
ORDER BY a.last_name, a.first_name SEPARATOR ' & ')
FROM authors a, author_books ab
WHERE ab.author_id = a.id
AND ab.book_id = b.id) author_name,
(SELECT GROUP_CONCAT(LOWER(CONCAT(a.last_name, '-', a.first_name))
ORDER BY a.last_name, a.first_name SEPARATOR ' & ')
FROM authors a, author_books ab
WHERE ab.author_id = a.id
AND ab.book_id = b.id) author_slug,
b.id,
b.title,
b.title_slug,
s.serie,
s.start_year,
s.serie_slug,
b.part,
b.pages,
b.published,
e.edition,
p.publisher,
f.format,
g.genre,
c.condition,
b.blurb
FROM books b,
series s,
editions e,
publishers p,
genres g,
formats f,
conditions c
WHERE b.serie_id = s.id
AND b.edition_id = e.id
AND b.publisher_id = p.id
AND b.format_id = f.id
AND b.genre_id = g.id
AND b.condition_id = c.id
I wouldn't even know where to begin.
1 like
Please or to participate in this conversation.