Laravel join query get second table data in a object

I am getting my data using inner join, but it's just appending second table's data in first ones, like this:

		"hadithNumber": 20,
		"hadithEnglish": "",
		"chapterId": 9,
		"bookSlug": "al-tirmidhi",
		"volume": 0,
		"status": "Sahih",
					
		// Second table data
		"chapterNumber": 9,
		"chapterEnglish": "The Book on Fasting",

But i want the second table data in an object like this:

		"hadithNumber": 20,
		"hadithEnglish": "",
		"chapterId": 9,
		"bookSlug": "al-tirmidhi",
		"volume": 0,
		"status": "Sahih",
		
		// Second table data
		"chapter" : {
						"chapterNumber": 9,
						"chapterEnglish": "The Book on Fasting",
		}

I even can't use Model Eleoquent relation for this, because I have 2 where clause, using eloquent relation didn't give me right data, so I am doing it without model eloquent with inner join. So, please tell me how can I get the second table data in array or object? I am stuck, I've googled a lot, but didn't find out any answer. My tables are Hadith and Chapters. My Query is:

		public function index() {
					$query = Hadith::query()->join("chapters", function($join){
											$join->on("chapters.chapterNumber", "=", "hadiths.chapterId")
        											->on("chapters.bookSlug", "=", "hadiths.bookSlug");
					});
		}

Hadith Model

		class Hadith extends Model
		{
			// Get the book that owns the hadith.
			public function book() {
    						return $this->belongsTo(Books::class, 'bookSlug', 'bookSlug');
			}

			// Get the chapter that owns the hadith.
			public function chapter() {
		    return $this->belongsTo(Chapters::class, 'chapterId', 'chapterNumber')->where('bookSlug', $this->book->bookSlug);
}

}

MichalOravec

4 years ago

Level 75

I don't know your models etc, so here is just an example

$users = User::with('chapter')->select('users.*')
    ->join('table1', 'users.id', '=', 'table1.user_id')
    ->join('table2', 'table1.id', '=', 'table2.table1_id')
    ->where('table2.something', 'foo')
    ->where('table2.something_else', 'bar')
    ->get();

zainshabir00

4 years ago

Level 1

@michaloravec I am unable to make chapter relation in my model, because i have where clause in my chapter belongsTo relation which didn't work in query using with(). You can check my previous question for that. That's why i was using join. My tables are Hadith and Chapters. My Query is:

		public function index() {
					$query = Hadith::query()->join("chapters", function($join){
											$join->on("chapters.chapterNumber", "=", "hadiths.chapterId")
        											->on("chapters.bookSlug", "=", "hadiths.bookSlug");
					});
		}

Hadith Model

		class Hadith extends Model
		{
			// Get the book that owns the hadith.
			public function book() {
    						return $this->belongsTo(Books::class, 'bookSlug', 'bookSlug');
			}

			// Get the chapter that owns the hadith.
			public function chapter() {
		    return $this->belongsTo(Chapters::class, 'chapterId', 'chapterNumber')->where('bookSlug', $this->book->bookSlug);
}

}

Please or to participate in this conversation.