public function PostForDataTable($idUser = null, $denunciada = null)
{
$table = PuAds::leftJoin('pu_pictures', 'pu_ads.id', '=', 'pu_pictures.pu_ad_id')->join('pu_types as t', 'pu_ads.pu_type_id', '=', 't.id')->join('users as u', 'pu_ads.user_id', '=', 'u.id')->join('pu_categories as ca', 'pu_ads.pu_category_id', '=', 'ca.id')->leftJoin('pr_providers as p', 'pu_ads.pr_provider_id', '=', 'p.id')->leftJoin('pu_comments as pc', 'pu_ads.id', '=', 'pc.pu_ad_id');
if ($denunciada != null) {
$table = $table->join('pu_complaints as puc', 'pu_ads.id', '=', 'puc.pu_ad_id');
} else {
$table = $table->leftJoin('pu_complaints as puc', 'pu_ads.id', '=', 'puc.pu_ad_id');
}
$table = $table->leftJoin('pu_likes as li', 'pu_ads.id', '=', 'li.pu_ad_id')->leftJoin('pr_types as prt', 'p.pr_type_id', '=', 'prt.id')->select('pu_ads.*', DB::raw('CONCAT("' . asset('') . '", pu_pictures.url) AS picture_publication'), DB::raw('COUNT(pc.id) as comentarios'), DB::raw('COUNT(li.id) as likes'), DB::raw('COUNT(puc.id) as total'), 'u.picture as picture_user', DB::raw('CONCAT("' . asset('') . '",p.picture_face) AS picture_provider'), DB::raw("(if(pu_ads.flagactive='1','Activo',(if(pu_ads.flagactive='0','Inactivo','-')))) as flagactive"), DB::raw("(if(t.name_type='premium','active','')) as premium"), DB::raw("(if(pu_ads.flagactive='1','lock',(if(pu_ads.flagactive='0','unlock','-')))) as estado"), DB::raw('CONCAT("' . asset('') . '", ca.picture) AS picture_category'), 'u.id as idUser', DB::raw('CONCAT(u.name, " ", u.lastname) AS name_user'), 'prt.name_type as name_type_provider', 'p.name_provider', 'ca.name_category', 'p.ranking as ranking_provider', 't.name_type', 'prt.id as id_type_provider');
if ($idUser != null) {
$table = $table->where('pu_ads.user_id', '=', $idUser);
}
$table = $table->orderBy('pu_ads.pu_type_id', 'desc')->orderBy('pu_ads.flagactive', 'desc')->orderBy('pu_ads.lastupdate', 'desc')->groupBy('pu_ads.id');
return $table;
}
