Sort results by a relationship column in Laravel 4

Question

Currently I have some methods that used together allow me to make requests to my application controlling the "withs", "Orders" and such via querystring. I can do, for example:

GET /users?sort=name&sex=female

In this case the application will return a list of female users and sorted by name.

What I need to do now is sort the results by the column of a related table. How Laravel does two queries when we use the dowry with(), sorting is only possible by a column of the model table that is searching. Is there any way to do what I want, besides mounting the query and the joins in hand by the Builder? I also saw that you can do with the sortBy of the Collection, but this same code that I have been dealing with paging and in this case doing by Collection would order only the data that already came from the bank.

Answer 1

I created a package laraerp/ordination to use in my projects. It has a trait that when adding it to your model, the orderby method is rewritten. With it you can send a request of type:

GET /cliente?by=pessoa.razao_apelido&order=desc

In the example the table Client has a belongsTo Person.

Follows tutorial link.

Answer 2

Example:

SQL of table creation

CREATE TABLE `creditos` (
  `creditoid` int(11) NOT NULL AUTO_INCREMENT,
  `descricao` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`creditoid`),
  UNIQUE KEY `creditoid_UNIQUE` (`creditoid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


CREATE TABLE `noticias` (
  `noticiaid` bigint(20) NOT NULL AUTO_INCREMENT,
  `creditoid` int(11) NOT NULL,
  `titulo` text COLLATE utf8_unicode_ci NOT NULL,
  `subtitulo` text COLLATE utf8_unicode_ci,
  `texto` text COLLATE utf8_unicode_ci NOT NULL,
  `data` date NOT NULL,
  `hora` time NOT NULL,
  `fotocapa` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` tinyint(4) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`noticiaid`),
  UNIQUE KEY `noticiaid_UNIQUE` (`noticiaid`),
  KEY `pk2creditoId_idx` (`creditoid`),
  CONSTRAINT `pk2creditoId` FOREIGN KEY (`creditoid`) REFERENCES `creditos` (`creditoid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

---

Models of these two tables (Eloquent)

Credit

<?php
class Credito extends Eloquent {
    public $table      = 'creditos'; 
    public $primaryKey = 'creditoid';
    public $timestamps = true;
    public function noticia()
    {
        return $this->hasMany('Noticia', 'creditoid');
    }           
    public function getDates()
    {
        return array('created_at','updated_at');
    }
}

News story

<?php
class Noticia extends Eloquent {
    public $table      = 'noticias'; 
    public $primaryKey = 'noticiaid';
    public $timestamps = true;  
    public function credito()
    {
        return $this->belongsTo('Credito', 'creditoid');
    }    
    public function getDates()
    {
        return array('created_at','updated_at');
    }
}

Research by the relationship between these tables making a like in the Description of Credit and order by in the Creditos.Descricao (relationship table)

Eloquent

Mode with

Noticia::with(array('Credito' => function($query){
        $query->where('creditos.descricao', 'like', '%a%');
        $query->orderBy('creditos.descricao');
    }))->get();

Mode with Join

Noticia::join('creditos', 'creditos.creditoid', '=', 'noticias.creditoid')
         ->where('creditos.descricao', 'like', '%a%')
         ->orderBy('creditos.descricao', 'desc')
         ->get();

Mode with, Join and orderby

Noticia::with('Credito')
        ->join('creditos', 'creditos.creditoid','=', 'noticias.creditoid')
        ->orderBy('creditos.descricao')
        ->get();

Query Builder

DB::table("noticias")
        ->join('creditos', 'creditos.creditoid', '=', 'noticias.creditoid')
        ->where('creditos.descricao', 'like', '%a%')
        ->orderBy('creditos.descricao', 'desc')
        ->get();

^{Obs: Was placed where and orderBy together on purpose, to show the various possibilities, being that can use only what needs that in the case would orderBy. Another thing if you can give an opinion use with the mode join is much more transparent}

References:

• SQL LIKE Operator

• Eloquent

• Query Builder

• Eager Loading

• Selects