0

This is my models:

//project model
class Project extends Model {
    .....
    public function items(){
        return $this->hasMany(ProjectItem::class,'project_id');
    }
}
//project items model
class ProjectItem extends Model{
    ...
 public function project(){
        return $this->belongsTo(Project::class);
    }
}

In my controller I want to get the collection with the count of project items $projects =

Project::Select(['id','title'])->Where([            
            ['company' , '=', $company->id]
        ])->withCount('items')->paginate(50);

But I get this error :

SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row (SQL: select id, title, (select id from project_items where projects.id = project_items.project_id) as items_count from projects where (company = 2) limit 50 offset 0)

What is the problem here? Why it doesn't use the SQL COUNT() function in the query, but instead still using SELECT?

I using jetstream inertia , hence will need collection in return. And I also don't want to load the relationship model in the collections as well.

Edit

Here is how I create the tables:

//projects table
Schema::create('projects', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->unsignedBigInteger('company');
            $table->foreign('company')->references('id')->on('company')->onDelete('cascade');//projects is belong to another company table.
            $table->timestamps();
        });

//project_items table
Schema::create('project_items', function (Blueprint $table) {
            $table->id();
            $table->string('title');  
            $table->longText('desc')->nullable();
            $table->unsignedBigInteger('project_id');
            $table->foreign('project_id')->references('id')->on('projects');
            $table->timestamps();
        });

The 'project' itself also belong to 'company' table. But I don't think is relevant since I only query the projects and the project items here .

Sorry because my bad English.

dev-jim
  • 2,404
  • 6
  • 35
  • 61

1 Answers1

0

can you replace your query with this and check

Project::select('id','title')->where('company' , $company->id)->withCount('items')->paginate(50);
Abdul Rehman
  • 222
  • 1
  • 5
  • tried but still got the same error – dev-jim Jan 01 '22 at 08:04
  • try to divide the query and check on which condition it's getting error. Is it with withCount. – Abdul Rehman Jan 01 '22 at 08:14
  • Yes, it is the withCount that caused the error. It working fine without the withCount, even with() also works. – dev-jim Jan 01 '22 at 08:31
  • I tried to generate something same but working fine for me. check if your withCount is working fine for other models or not. if not maybe you are using an older mysql version. – Abdul Rehman Jan 01 '22 at 08:42
  • I tried with other models, yes, it is working fine. I also updated the question with the migration i use to create the tables. Not sure if it is relevant. Thanks – dev-jim Jan 01 '22 at 10:09
  • Ok, found the problem. It was I added the global scope `static::addGlobalScope` in the projectItem model, it will override the Count function. Nevertheless, still thanks for you help. – dev-jim Jan 01 '22 at 10:25