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);
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);
}
}
© 2024 OneMinuteCode. All rights reserved.