One to many relationship with Laravel

Question

Okay, I must be missing something simple here.

I have the 'kjv' table, each row is a verse from the kjv bible:

CREATE TABLE `kjv` (
  `id` int(11) NOT NULL,
  `book` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL,
  `chapter` smallint(6) DEFAULT NULL,
  `verse` int(11) DEFAULT NULL,
  `contents` mediumtext COLLATE utf8mb4_unicode_ci
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

;

I also have a commentary table, each row has a commentary on a particular verse.

CREATE TABLE `commentary` (
  `id` int(11) NOT NULL,
  `kjv_id` int(11) NOT NULL,
  `note` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` tinyint(4) NOT NULL COMMENT 'BOOL',
  `intro` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Okay. So, as I understand it, the I can fetch the commantary this way in my KJVController:

$results =   KJV::where('book', '=', $this->book)
            ->where('chapter', '=', $this->chapter)
            ->leftJoin('commentary', 'kjv.id', '=', 'kjv_id')
            ->get();
dd($results);

Which is great, but I don't get multiple commentaries when I display the results.

So, as per this example I added this function in the KJV model:

class KJV extends Model
{
    protected $table = "kjv";
    function commentary() {
        return $this->hasMany(Commentary::class);
    }
}

And added use App\Commentary; in my KJVController. Now again, as per the example, I should be able to reference ->comments somewhere in the KJV::where query, and remove the leftJoin. But no matter what I do I get errors.

what happens if you do ```KJV::where('book', '=', $this->book)->with('comentary') ->where('chapter', '=', $this->chapter) ->get();``` — Leo, Feb 22 '18 at 14:47
@LeoinstanceofKelmendi I get SQLSTATE[42S22]: Column not found: 1054 Unknown column 'commentary.k_j_v_id' in 'where clause' (SQL: select * from `commentary` where `commentary`.`k_j_v_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31))" — Chud37, Feb 22 '18 at 15:04
it better to use InnoDB engine instead of MyISAM when your database it relational, you can read more in this url : https://stackoverflow.com/questions/15678406/when-to-use-myisam-and-innodb — Ahmed Bermawy, Feb 22 '18 at 15:21

score 2 · Accepted Answer · answered Feb 22 '18 at 14:47

2

You will be able to access them like this:

KJV::where('book', $this->book)->where('chapter', $this->chapter)->first()->commentary

You don't need get() and also a side note: you don't need to specify = inside where

PS: Make sure the commentaries have their kjv_id set to the KJV ID you want to retrieve.

PS 2: if you want to retrieve all the commentaries for multiple kjvs, you can as follow:

KJV::where('book', $this->book)->where('chapter', $this->chapter)->with('commentary')->get()

answered Feb 22 '18 at 14:47

Sletheren

2,435
11
25

Hi! Yes the kjv_id is correctly set in the table. When I try your code I get: `Undefined property: Illuminate\Database\Eloquent\Builder::$commentary` – Chud37 Feb 22 '18 at 14:52
Now i'm getting "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'commentary.k_j_v_id' in 'where clause' (SQL: select * from `commentary` where `commentary`.`k_j_v_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31))" – Chud37 Feb 22 '18 at 15:04
@Chud37 because you named your table I guess KJV instead of kjv.. laravel converst uppercase to A_B_C... – Sletheren Feb 22 '18 at 15:04
Take a look on the docs: `In the example above, Eloquent will try to match the user_id from the Phone model to an id on the User model. Eloquent determines the default foreign key name by examining the name of the relationship method and suffixing the method name with _id. However, if the foreign key on the Phone model is not user_id, you may pass a custom key name as the second argument to the belongsTo method:` – Sletheren Feb 22 '18 at 15:08
Oh wow, thanks! That makes sense actually. I'm away from my pc now but I'll give it a go as soon as i can. – Chud37 Feb 22 '18 at 15:26
Thank you so much that did it! I renamed my model and it worked straight away. – Chud37 Feb 22 '18 at 19:31
@Chud37 glad it helped :) – Sletheren Feb 23 '18 at 09:45

One to many relationship with Laravel

1 Answers1