Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Please tell me how can I select from the database all the posts using sorting pivot based tables? Have a table Posts and Users as well as the junction table post_user. Pivot based table has the following format: https://www.dropbox.com/s/g825eruqpi4e8pk/323.jpg?dl=0 I need to select all the posts from the database in which all status "cheked" using data from a pivot based tables. The figure marked one post with these settings. Please tell me how can I build a query or what conditions it is possible to build a query.
Sure it's possible, you just need to do a belongsToMany relationship.
Look at the documentation: http://laravel.com/docs/5.0/eloquent#many-to-many
and to verify your checked column look here: http://laravel.com/docs/5.0/eloquent#querying-relations
@RomainLanz I have relationships in my models. Here are my models of Posts and Users.
class Post extends Eloquent {
public function users()
{
return $this->belongsToMany('User');
}
}
class User extends Cartalyst\Sentry\Users\Eloquent\User implements UserInterface, RemindableInterface {
public function posts()
{
return $this->belongsToMany('Post');
}
}
Please tell me how can I make my request. I'm new to Laravel
@azuron You want to sort by the field in pivot or related table, or just filter by that field? It's quite a difference.
@JarekTkaczyk I need to select all the posts from the database using the data in pivot based table. I need all the posts which pivot based table status "cheked". This means that this post is checked and it should show in the archive. The photo shows what posts I need to get in the archive.
@azuron So here it goes:
// using eager loading constraints
$user = User::with(['posts' => function ($q) {
$q->wherePivot('checked', 'checked');
}])->find($userId);
// OR using lazy eager loading
$user->load(['posts' => function ($q) {
$q->wherePivot('checked', 'checked');
}]);
// then
$user->posts; // collection of checked posts only
// OR without loading the relation on $user object
$checkedPosts = $user->posts()->wherePivot('checked', 'checked')->get();
Also, I suggest you use bool type for this field instead of checked / notChecked values:
$table->boolean('checked')->default(false);
then you do:
->wherePivot('checked', true/false);
and it returns value that evaluates to either true or false. While now, both values being strings, evaluate to true.
I wrote as you said. My controller now looks like this.
public function getArchive()
{
$user = Sentry::getUser();
$userId = $user->id;
// using eager loading constraints
$user = User::with(['posts' => function ($q) {
$q->wherePivot('checked', 'checked');
}])->find($userId);
// OR using lazy eager loading
$user->load(['posts' => function ($q) {
$q->wherePivot('checked', 'checked');
}]);
// then
$user->posts; // collection of checked posts only
// OR without loading the relation on $user object
$posts = $user->posts()->wherePivot('checked', 'checked')->get();
return View::make('archive')->with('posts', $posts);
}
In the end, I got the error.
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'post_user.checked' in 'where clause' (SQL: select `posts`.*, `post_user`.`user_id` as `pivot_user_id`, `post_user`.`post_id` as `pivot_post_id` from `posts` inner join `post_user` on `posts`.`id` = `post_user`.`post_id` where `post_user`.`user_id` in (16) and `post_user`.`checked` = checked)
@azuron First off, you don't need all the pieces - pick one :) If you want to show only posts, then last one will do.
Next, the error comes from the fact, that you have a typo: cheked instead of checked - so simply change the code so you use cheked field and value.
@JarekTkaczyk Please forgive me for the mistake, I didn't see her. I changed my controller so but get the error:
public function getArchive()
{
$user = Sentry::getUser();
$userId = $user->id;
$posts = $user->posts()->wherePivot('checked', 'checked')->get();
return View::make('archive')->with('posts', $posts);
}
Call to undefined method Illuminate\Database\Query\Builder::posts()
Apparently I have something wrong? Sorry but I don't why why I get an error.
@azuron Look for another typo somewhere. This error may occur when you are trying to call $model->nonExistingMethod, and from what you pasted above, you definitely have posts method on the user model.
@JarekTkaczyk Thank you very much for your support and help! You are right I have additional entries in the models. Surely they can affect the operation of the methods? I see no errors in them. Here are my model entirely:
class Post extends Eloquent {
protected $table = 'posts';
public $timestamps = true;
protected $guarded = array('sender');
public function city()
{
return $this->hasOne('City');
}
public function users()
{
return $this->belongsToMany('User');
}
}
use Illuminate\Auth\UserTrait;
use Illuminate\Auth\UserInterface;
use Illuminate\Auth\Reminders\RemindableTrait;
use Illuminate\Auth\Reminders\RemindableInterface;
class User extends Cartalyst\Sentry\Users\Eloquent\User implements UserInterface, RemindableInterface {
use UserTrait, RemindableTrait;
protected $table = 'users';
protected $hidden = array('password', 'remember_token');
public function posts()
{
return $this->belongsToMany('Post');
}
}
@azuron It looks fine. Are you sure you are using this model? :)
Try this simple route (or do it in cli with artisan tinker):
Route::get('testuser', function () {
return User::first()->posts; // it must return a collection, it may be empty though
});
@JarekTkaczyk Yes I am using these models, I have them all 3. Here they are: https://www.dropbox.com/s/9jy1rwpy0zqs278/%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202015-02-27%20%D0%B2%2019.07.20.png?dl=0 When I added the route to a file routes.php I get [ ]. When I run artisan tinker in the terminal I get [1]. Perhaps I made somewhere a mistake:)
@azuron You just don't have any posts for this user, that's all I suppose.
@JarekTkaczyk I checked, logged in as different users and created posts. But the result remained the same:) I'm sorry that I bother you so long your questions)) I think I'll have to alter the structure of your app, I think it's not right that I'm having such problems with the choice of posts and sorting data. Thank you very much for your support and help!!!
@azuron You've shown City model with posts relation :) Check the models again.
@JarekTkaczyk Here are my tables, you can see how they relate. https://www.dropbox.com/s/fi5c1lhadtetvai/%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202015-02-27%20%D0%B2%2020.57.14.png?dl=0 And here are my 3 classes.
class City extends Eloquent {
protected $table = 'cities';
public $timestamps = false;
public function posts()
{
return $this->hasMany('Post');
}
}
class Post extends Eloquent {
protected $table = 'posts';
public $timestamps = true;
protected $guarded = array('sender');
public function users()
{
return $this->belongsToMany('User');
}
public function city()
{
return $this->hasOne('City');
}
}
use Illuminate\Auth\UserTrait;
use Illuminate\Auth\UserInterface;
use Illuminate\Auth\Reminders\RemindableTrait;
use Illuminate\Auth\Reminders\RemindableInterface;
class User extends Cartalyst\Sentry\Users\Eloquent\User implements UserInterface, RemindableInterface {
use UserTrait, RemindableTrait;
protected $table = 'users';
protected $hidden = array('password', 'remember_token');
public function posts()
{
return $this->belongsToMany('Post');
}
}
Everything looks correct but for some reason does not work method posts(). I wrote my models again but the result is still((
@azuron Show the tables with (some) data.
@azuron and the code you execute? Which use in particular? And the query as well ;)
@JarekTkaczyk Yes everything works fine. Can't display only those posts. I need to display all the posts in the archive are reviewed by a moderator. I thought that it is possible to implement it this way, add field checked in which the recorded status checked the post or not. Maybe there is way more simple and right to implement this.
@azuron This is the way. You just made a mistake somewher. Have you fixed checked -> cheked everywhere?
@JarekTkaczyk Changed as you pointed everywhere checked -> cheked. Here is my pivot table: https://www.dropbox.com/s/t53r9qolfeodh22/%D0%A1%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA%20%D1%8D%D0%BA%D1%80%D0%B0%D0%BD%D0%B0%202015-02-27%20%D0%B2%2022.00.20.png?dl=0 Here is my controller:
public function getArchive()
{
$user = Sentry::getUser();
$userId = $user->id;
$posts = $user->posts()->wherePivot('checked', 'cheked')->get();
return View::make('archive')->with('posts', $posts);
}
I get the error
Call to undefined method Illuminate\Database\Query\Builder::posts()
@azuron Are you sure Sentry uses your User model, not its default?
@JarekTkaczyk I use the package Sentry 2 for user's login and work with all users. Here is what is written in my User model:
class User extends Cartalyst\Sentry\Users\Eloquent\User implements UserInterface, RemindableInterface
I don't quite understand the dependence of all classes and models. Apparently the User model uses Sentry. If I understand everything correctly.
@azuron Finally we have the culprit ;)
Sentry has config entry:
/*
|--------------------------------------------------------------------------
| Model
|--------------------------------------------------------------------------
|
| When using the "eloquent" driver, we need to know which
| Eloquent models should be used throughout Sentry.
|
*/
'model' => 'Put\Your\Model\Here',
Do you think the problem is in Sentry? Here is what I have written in the configuration file Sentry.
/*
|--------------------------------------------------------------------------
| Model
|--------------------------------------------------------------------------
|
| When using the "eloquent" driver, we need to know which
| Eloquent models should be used throughout Sentry.
|
*/
'model' => 'Cartalyst\Sentry\Groups\Eloquent\Group',
),
/*
|--------------------------------------------------------------------------
| Users
|--------------------------------------------------------------------------
|
| Configuration specific to the user management component of Sentry.
|
*/
'users' => array(
/*
|--------------------------------------------------------------------------
| Model
|--------------------------------------------------------------------------
|
| When using the "eloquent" driver, we need to know which
| Eloquent models should be used throughout Sentry.
|
*/
'model' => 'Cartalyst\Sentry\Users\Eloquent\User',
@azuron cmon, this is generic Sentry user, you need to put there your model. I guess it's App\User, or without namespace. right?
@JarekTkaczyk With this method I have not the site)) I try an alternative way:
public function getArchive()
{
$user = Sentry::getUser();
$userId = $user->id;
$posts = Post::whereHas('users', function ($q) use ($userId) {
$q->where('user_id', '=', $userId)->where('checked', '=', 'cheked');
})->get();
return View::make('archive')->with('posts', $posts);
}
you will receive 3 post 2 of which are endorsed by all users and 1 post is approved in part.
Please or to participate in this conversation.