Table JOIN + additional questions left join query query builder representation by tying to the label Eloquent relationship

Hi, everyone. Using the label Eloquent to import table data, at the end of the search

belongsto()
hasMany()

I knew I had to use it, but when I searched, I found that I used with() and I also used join().Then I don't see what's different from DB:join() or how I can use it, so I ask.

// Bulletin information
<?php
namespace App\Http\Board\table;

use Illuminate\Database\Eloquent\Model;

//Board
class Board extends Model {

    public $timestamps = false;

    protected $table = 'board';

    protected $primaryKey = 'board_id';


    protected $fillable = [
        "board_type","header","subject_length_limit","page_list_cnt"
    ];

    // N:1 (Board: Bulletin)
    public function content(){
        return $this->hasMany('App\Http\Board\Table\BoardContent', 'board_id');
    }

}

//About the bulletin table
<?php
namespace App\Http\Board\Table;

use Illuminate\Database\Eloquent\Model;

//Posting
class BoardContent extends Model {

    public $timestamps = false;

    protected $table = 'content';

    protected $primaryKey = 'seq';

    protected $fillable = [
        "board_id","writer","subject","content","comment_cnt","date"
    ];

    // N:1 (Poster: Bulletin)
    public function board(){
        return $this->belongsTo('App\Http\Board\Table\Board', 'board_id');
    }
    // 1:N (posted: comment)
    public function comment(){
        return $this->hasMany('App\Http\Board\Table\BoardComment', 'seq');
    }

}

 //Board List
    public function BoardList(){
        $boardList = Board::with('content')
            ->join('content', 'board.board_id', '=', 'content.board_id')
            ->select("count(*) as cnt", "board.*")
            ->get();
        return [
            "boardList" => $boardList
        ];
    }

+++ Additionally, I didn't know how to apply it as a current query builder, so I temporarily wrote down raw sql, but I wonder how to use it as a query builder while using Eloquent above.

DB::select("SELECT A.*, IFNULL(B.state, 0) as 'count'
     FROM board as A 
     LEFT OUTER JOIN 
        ( SELECT board_id, count(board_id) as state 
            FROM content
                GROUP BY board_id 
         ) as B on (A.board_id = B.board_id) GROUP BY A.board_id");

DB works.

DB::table("board as A")
            ->select("A.*",DB::raw(" IFNULL(B.state, 0) as 'count'"))
            ->leftJoin(DB::raw("( SELECT board_id, count(board_id) as state FROM content GROUP BY board_id ) as B"),"A.board_id","=","B.board_id")
            ->groupBy("A.board_id")
            ->paginate(3);

laravel php

2022-09-22 19:03

1 Answers

You defined the content() method in the Board model now, right? Because the method defines a typical relationship, hasMany(), with() and join() that you had to write to get the posts on the bulletin board.

// If you import the bulletin board,
$theBoard = Board::find($id)->get();

// You can get posts from there like this.
$theContentsOnTheBoard = $theBoard->content()->get();

+ Assuming that the SQL you uploaded is to count and import the 'Board with Posts', this can be roughly changed.

// on the App\Http\Board\table\Board model
// Adding an accessor in the form of a word attribute() that starts with a "get uppercase letter" allows you to process the desired property
public function getTheStateAttribute() {
  return $this->content()->count();
}

// When you use it from a controller or another model,
use App\Http\Board\table\Board;
$boardsWithCount = Board:where('the_state', '>', 0)->get(); //
foreach ($boardsWithCount as $board) {
  var_export ($board->the_state); // Something like that.
  foreach ($board->content() as $content) {
    var_dump($content->board_id);
  }
}

2022-09-22 19:03

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656