1. Home
2. php - Laravel Eloquent thee tables relationship query

518 votes

1 answers

php - Laravel Eloquent thee tables relationship query

Get the solution ↓↓↓

I have three mysql tables :

• students (constrained withUSER_ID)
• student_in_tournament (constrained withSTUDENT_ID andTOURNAMENT_ID)
• tournaments

I need an eloquent query who take ALL students by specifiedUSER_ID and if these students are in the student_in_tournament table, it needs to join with the tournament table.

If these students are not in the student_in_tournament, they don't need to join something.

At the end, I need ALL students from one specificUSER_ID, with the tournament joined if he exists in the student_in_tournament table...

I tried a inner join, but it don't give me ALL the students, only these who are in the student_in_tournament table. I also triedStudent::with('studentInTournament.tournaments') but it gives me 'null' in the tournaments

Thanks

Undefined asked

2021-12-1

Write your answer

---

552

votes

Answer

Solution:

Try creating a M:N relation between yourStudent andTournament models. Since your table and column names do not conform to what Eloquent expects, you'll need to pass all 4 parameters when creating the relationship.

https://laravel.com/docs/8.x/eloquent-relationships#many-to-many-model-structure

// Student model
public function tournaments()
{
    return $this->belongsToMany(Tournament::class, 'student_in_tournament', 'STUDENT_ID', 'TOURNAMENT_ID');
}
Copy code

https://laravel.com/docs/8.x/eloquent-relationships#many-to-many-defining-the-inverse-of-the-relationship

// Tournament model
public function students()
{
    return $this->belongsToMany(Student::class, 'student_in_tournament', 'TOURNAMENT_ID', 'STUDENT_ID');
}
Copy code

$students = Student::query()
    ->where('USER_ID', $user_id) // or whereIn('USER_ID', $user_ids) if you have multiple user ids
    ->with('tournaments')
    ->get();    
Copy code

https://laravel.com/docs/8.x/blade#loops

@foreach($students as $student)
  <!-- show student information -->
  @forelse($student->tournaments as $tournament)
    <!-- show tournament information -->
  @empty
    <!-- student has no tournaments -->
  @endforelse
  <hr>
@endforeach
Copy code

Write your answer

---

Share solution ↓

Additional Information:

Date the issue was resolved:

2021-12-1

Link To Source
Link To Answer People are also looking for solutions of the problem: xml parsing error: no root element found

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

---

Similar questions

Find the answer in similar questions on our website.

497 php - Save data to two different tables in CakePHP 3

789 PHP + SQL - Using SQL Server 'Create To' scripts to create tables through PHP

385 php - Using PHPThumb and Thickbox/Colorbox

874 php - Cakephp 2.8.4 ignoring hasMany

23 php - function query() on a non-object error

143 php - Nested mysql_query() - Resets first result's pointer?

368 php - Laravel Middleware Cache not working properly

167 php - Multi-tenancy - Create tables up-front or as needed?

202 php,mysql,html - Display SQL query(conjunction) properly in HTML

840 php - Using JQuery UI's sortable('serialize') after loading data in via .load();

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

About the technologies asked in this question

PHP

PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites. The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/

Laravel

Laravel is a free open source PHP framework that came out in 2011. Since then, it has been able to become the framework of choice for web developers. One of the main reasons for this is that Laravel makes it easier, faster, and safer to develop complex web applications than any other framework.
https://laravel.com/

MySQL

DBMS is a database management system. It is designed to change, search, add and delete information in the database. There are many DBMSs designed for similar purposes with different features. One of the most popular is MySQL. It is a software tool designed to work with relational SQL databases. It is easy to learn even for site owners who are not professional programmers or administrators. MySQL DBMS also allows you to export and import data, which is convenient when moving large amounts of information.
https://www.mysql.com/