Merge two records having same id

i have a join query of two tables

PROMOS: 
id
name
icon_url
challenge
etc...

 HORARIOS:
days
hours
promo_id

Every promo can have two horarios, this means that some records only have one horario, but others, two horarios, thats why i use join

DB::table('promos')
                        ->join('horarios', function($join) use ($quehoraes){
                          $join->on('horarios.promo_id','=','promos.id');
                        })            ->select('promos.id','promos.name','promos.icon_url','promos.challenge','promos.img_reco','promos.latitude','promos.longitude','horarios.clicks','horarios.hours','horarios.days')
                        ->where('status',1)
                        ->get();

so as a result i have the records from the query, when a promo only have one horario i receive a record, but if the promo has two horarios i receive two records with the same id but with diferent info in days and hours.

[
    {
        "id": 1,
        "name": "Camellon",
        "icon_url": "loquesea.jpg",
        "challenge": "Debe hacer una primaide de al menos 3 personas y gritar fuerte QUIERO MI HELADO DE CHOLOCATE",
        "img_reco": "foto1.jpg",
        "clicks": 10,
        "hours": 12,
        "days": "2"
    },
    {
        "id": 2,
        "name": "Vito Alessio",
        "icon_url": "loquesea.jpg",
        "challenge": "¿Cual es el tamaño de un taco al pastor?",
        "img_reco": "foto1.jpg",
        "clicks": 21,
        "hours": 12,
        "days": "1"
    },
    {
        "id": 2,
        "name": "Vito Alessio",
        "icon_url": "loquesea.jpg",
        "challenge": "¿Cual es el tamaño de un taco al pastor?",
        "img_reco": "foto1.jpg",
        "clicks": 43,
        "hours": 13,
        "days": "0"
    },
    {
        "id": 3,
        "name": "Kiosco",
        "icon_url": "loquesea.jpg",
        "challenge": "Debes pedirlo asi, soy un payaso y deseo mis productos",
        "img_reco": "foto1.jpg",
        "clicks": 32,
        "hours": 15,
        "days": "1,3,6,2"
    },
    {
        "id": 5,
        "name": "Parisina",
        "icon_url": "loquesea.jpg",
        "challenge": "Telas parisina",
        "img_reco": "foto1.jpg",
        "clicks": 54,
        "hours": 19,
        "days": "2,3,4"
    },
    {
        "id": 7,
        "name": "cafesito rico",
        "icon_url": "loquesea.jpg",
        "challenge": "ponte a brincar",
        "img_reco": "foto1.jpg",
        "clicks": 21,
        "hours": 7,
        "days": "4,2,3"
    }
]

I need to get only one record from every promo, and in the case it has two horarios, the field clicks, hours, days merge the content of the two records in one.

My first aproach is to process the collection with map function and apply a function, but i cant see how to do the work.

Thanks.

lostdreamer_nl

7 years ago

Level 53

Why are you not using Eloquent models? It would have made this a lot easier:

$promos = Promo::with('horarios')->where('status', 1)->get();

Create your Promo model:

namespace App;

use Illuminate\Database\Eloquent\Model;

class Promo extends Model
{
    protected $fillable = ['all', 'fillable', 'fields', 'here'];
    
    public function horarios() 
    {
        return $this->hasMany(Horario::class);
    }
}

Create your Horariomodel:

namespace App;

use Illuminate\Database\Eloquent\Model;

class Horario extends Model
{
    protected $fillable = ['all', 'fillable', 'fields', 'here'];
    
    public function promo() 
    {
        return $this->belongsTo(Promo::class);
    }
}

And you can do what I said above: Promo::with('horarios')->get();

Giving you a collection of Promo models, each one having a key 'horarios' with a collection of the Horario models

Try to only use the DB class if you cannot do it without.

plutarcomp

7 years ago

Level 1

Thanks a lot @lostdreamer_nl , your advice is very useful

Please or to participate in this conversation.