mardi 2 juin 2020

Error in laravel multiple union query in foreach loop

I am facing issue in laravel union query in foreach loop.

  $query1 = DB::table('tbl_document_types');
  $query1->where('tbl_document_types.is_app',0);
  $query1->orderBy('tbl_document_types.document_type_order', 'ASC');
  $document_types = $query1->get();

  $length=10;
  $loop=0;

  foreach($document_types as $k => $v)
  {    
    $tbl_documents = 'tbl_documents';
    $document_coloumn = 'tbl_document_column_'. $v->document_type_id;
    $document_type_name = $v->document_type_name; 
    if(Schema::hasTable($document_coloumn))
    {

        $query = DB::table($tbl_documents);
        $query->join($document_coloumn, $document_coloumn . '.' . 'document_id', '=', $tbl_documents . '.' . 'document_id');
        $query->select("$tbl_documents.document_id","$tbl_documents.document_type_id");
        if($loop < 1)
        {
            $union_query = $query;

        }
        else
        {
            //$union_query->select("$tbl_documents.document_id","$tbl_documents.document_type_id");
            $union_query->union($query);
        }
        $loop++;
    }
  }
  $result = $union_query->paginate($length);

I am getting following mysql error

SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: 
(select count(*) as aggregate from `tbl_documents` inner join `tbl_document_column_107` on `tbl_document_column_107`.`document_id` = `tbl_documents`.`document_id`) 
union (select `tbl_documents`.`document_id`, `tbl_documents`.`document_type_id` from `tbl_documents` inner join `tbl_document_column_111` on `tbl_document_column_111`.`document_id` = `tbl_documents`.`document_id`) 
union (select `tbl_documents`.`document_id`, `tbl_documents`.`document_type_id` from `tbl_documents` inner join `tbl_document_column_134` on `tbl_document_column_134`.`document_id` = `tbl_documents`.`document_id`) 
union (select `tbl_documents`.`document_id`, `tbl_documents`.`document_type_id` from `tbl_documents` inner join `tbl_document_column_140` on `tbl_document_column_140`.`document_id` = `tbl_documents`.`document_id`) 
union (select `tbl_documents`.`document_id`, `tbl_documents`.`document_type_id` from `tbl_documents` inner join `tbl_document_column_141` on `tbl_document_column_141`.`document_id` = `tbl_documents`.`document_id`))

One "tbl_documents" and multiple "tbl_document_column" for each document type. How to write laravel union query in for each loop?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire