How to select last related table records?

Question

I have the following tables: negociacoes, negociacao_contatos and negociacao_status and the following relationships:

negociacoes hasMany negociacao_contatos

negociacoes belongsTo negociacao_status

In negociacao_status I have two fields: alerta_usuario (Y/N) and prazo_alerta (int).

I need to do a search (COUNT) of negociacoes in which his status (negociacao.negociacao_status_id) be it alerta_usuario = "Y" and negociacao_contatos.created is greater than prazo_alerta.

That is, I need to count how many negotiations there are in which the last contact was held more than X days ago. How would be the most correct way to do this search? I have tried several alternatives, none successfully.

Answer 1

If I understand correctly it will be something like this:

The query:

SELECT COUNT(*) as count FROM negociacao_status as ns 
INNER JOIN negociacoes         as n  on n.negociacoes_status_id = ns.id 
INNER JOIN negociacao_contatos as nc on nc.negociacoes_id       = n.id  
WHERE ns.alerta_usuario = "Y" AND nc.created > ns.prazo_alerta ;

Answer 2

The way I (finally) solved the problem was as follows:

SELECT 
Negociacao.id,Cliente.nome,NegociacaoStatus.nome,NegociacaoContato.created,DATEDIFF(NOW(),NegociacaoContato.created) AS dias
FROM negociacoes AS Negociacao
LEFT JOIN 
    (
        SELECT NC.negociacao_id,NC.created,max(NC.id) AS id 
        FROM negociacao_contatos AS NC 
        GROUP BY negociacao_id
    ) AS NegociacaoContato2
ON NegociacaoContato2.negociacao_id = Negociacao.id
LEFT JOIN negociacao_status AS NegociacaoStatus 
ON Negociacao.negociacao_status_id = NegociacaoStatus.id
LEFT JOIN clientes AS Cliente
ON Negociacao.cliente_id = Cliente.id
LEFT JOIN negociacao_contatos AS NegociacaoContato
ON NegociacaoContato.id = NegociacaoContato2.id
WHERE NegociacaoStatus.finaliza != "Y"
AND Negociacao.consultor_id = 23
AND Negociacao.ativo = "Y"
AND NegociacaoStatus.alerta_usuario = "Y"
AND datediff(now(),NegociacaoContato.created) >= NegociacaoStatus.prazo_alerta
ORDER BY dias DESC

I thank everyone for their contributions. Through them I managed to reach this result.

Answer 3

Following the same table settings as Jorge B. but without the use of JOIN would look like this:

    select count(*) 
    from negociacoes n, negociacao_contatos nc, negociacao_status ns       
    where ns.id = n.negociacoes_status_id 
      and n.id = nc.negociacoes_id 
      and ns.alerta_usuario='Y' 
      and nc.created > ns.prazo_alerta;

Whereas the tables are like this:

CREATE TABLE `negociacao_contatos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` varchar(45) DEFAULT NULL,
  `negociacoes_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `negociacao_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alerta_usuario` varchar(45) DEFAULT NULL,
  `prazo_alerta` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `negociacoes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `negociacoes_status_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;