whereNull give me a different result than doesntHave

@vincent15000

To be sure, you can have a look to the 2 sql generated.

@JeremieB With your filter with doesntHave, you check nested relationships whereas with your filter with whereNull, you only check the on the item itself and not via its relationships.

@vincent15000 You are right that it is not exactly the same, but since my whereNull return me some result, that means that there are some of x that doesnt have a relation prelevable.essai.rapport because prelevable would be null.

@JeremieB Not necessarily ... You can have this.

X with some prelevable null.

x1 : prelevable_id = null
x15 : prelevable_id = null
x32 : prelevable_id = null

And some other x that have prelevable and essai but no rapport.

x2 : prelevable.essai.rapport = null
x4 : prelevable.essai.rapport = null
x8 : prelevable.essai.rapport = null

I think that doesntHave retrieves only X with prelevable not null but where essai.rapport is null (essai not null and rapport null). That's why you have this difference in your results.

As @sr57 said, I should do some simple tests to be sure it is exact. I don't have this configuration in my database, but you have it, you can check the resulting SQL query for both queries. You have just to display them, for example with dd().

@vincent15000

I think that doesntHave retrieves only X with prelevable not null but where essai.rapport is null (essai not null and rapport null). That's why you have this difference in your results.

That's why I proposed:

$q->doesntHave("prelevable")->orDoesntHave("prelevable.essai")->orDoesntHave("prelevable.essai.rapport");

Even this does not retrieve a single result which do the same thing has whereNull("prelevable_id"):

$q->doesntHave("prelevable")

Here is what `$q->doesntHave("prelevable.essai.rapport");`` return me in sql

select * from `ORIGINAL_TABLE` where ((`ORIGINAL_TABLE`.`prelevable_type` = ? and not exists (select * from `prelevements` where `ORIGINAL_TABLE`.`prelevable_id` = `prelevements`.`id` and exists (select * from `essais` where `prelevements`.`essai_id` = `essais`.`id` and exists (select * from `rapports` where `essais`.`idRapport` = `rapports`.`id` and `rapports`.`type` = ?))))) order by `preleve_le` desc limit 100

When I try to run this SQL in tableplus, I get an sql format error, but if I remove ORIGINAL_TABLE.prelevable_type = ? and rapports.type = ?, the sql query return me some result.

I think laravel could replace the ? by a type and since the type is null, it does not work !

@JeremieB

Laravel (the dbms) replaces the ? by your parameters (sql injection protection), to test in db don't remove to code just replace the ? by real parameters.

@sr57 The problem is that it can be XXX, but it could also be null because I would simply don't have a prelevable.

So, I need to have all my list of type and null, but laravel seems to only take the list of type.

@JeremieB

Yes, in this query ... that should explain what @vincent15000 wrote.

@sr57 I edited my previous message. Is it clearer now?

@JeremieB Can you send here a set of datas in your database (3 or 4 lines in each table to illustrate your needs) ?

And then can you show what you need to retrieve with query 1 and then with query 2 ?

I mean it would be perhaps easier to help you with concrete datas.

@vincent15000

Here is a dump. I totally forget to put the auto_increment, but it should be ok for you

-- MySQL dump 10.13  Distrib 8.0.18, for Win64 (x86_64)
--
-- Host: 127.0.0.1    Database: test
-- ------------------------------------------------------
-- Server version	8.0.27

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `essai`
--

DROP TABLE IF EXISTS `essai`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `essai` (
  `id` int NOT NULL,
  `rapport_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rapport_id` (`rapport_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `essai`
--

LOCK TABLES `essai` WRITE;
/*!40000 ALTER TABLE `essai` DISABLE KEYS */;
INSERT INTO `essai` VALUES (0,0),(1,1);
/*!40000 ALTER TABLE `essai` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `metal`
--

DROP TABLE IF EXISTS `metal`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `metal` (
  `id` int NOT NULL,
  `essai_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `essai_id` (`essai_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `metal`
--

LOCK TABLES `metal` WRITE;
/*!40000 ALTER TABLE `metal` DISABLE KEYS */;
INSERT INTO `metal` VALUES (0,0),(1,1);
/*!40000 ALTER TABLE `metal` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `original_table`
--

DROP TABLE IF EXISTS `original_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `original_table` (
  `id` int NOT NULL,
  `prelevable_id` int DEFAULT NULL,
  `prelevable_type` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `original_table`
--

LOCK TABLES `original_table` WRITE;
/*!40000 ALTER TABLE `original_table` DISABLE KEYS */;
INSERT INTO `original_table` VALUES (0,NULL,NULL),(1,0,'metal'),(2,NULL,NULL);
/*!40000 ALTER TABLE `original_table` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `rapport`
--

DROP TABLE IF EXISTS `rapport`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `rapport` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `rapport`
--

LOCK TABLES `rapport` WRITE;
/*!40000 ALTER TABLE `rapport` DISABLE KEYS */;
INSERT INTO `rapport` VALUES (0),(1);
/*!40000 ALTER TABLE `rapport` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping routines for database 'test'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-06-03  9:45:42

@JeremieB Ok thank you, that will help. But where are the datas ? and the example of what you are waiting for with each query ? Difficult to explain in english ... I think you are french, so I explain in french.

Pour mieux pouvoir vous aider, il faudrait que vous nous donniez des exemples concrets. 3 ou 4 lignes de données pour chaque table et ensuite qu vous puissiez nous dire : voilà, avec cette requête, j'aimerais que ce soit ces données qui sortent, et avec l'autre requête, je voudrais que ce soit ces données-là qui sortent.

@vincent15000 Très bien.

Il y a déjà les données dans mon dump sql.

• original_table a une relation polymorphique avec metal (prelevable contient metal)
• metal est relié à essai.
• essai est relié à rapport.

Exemple: Pour ce qui concerne les exemples, les voici: J'aimerais que: $q->doesntHave("prelevable.essai.rapport") return le id 0 et 2 de original_table J'aimerais que: $q->has("prelevable.essai.rapport") return le id 1 de original_table

@JeremieB Ah oui désolé je n'avais pas vu les insert dans le dump ... ok pour les exemples ... je vais faire des tests de mon côté et je reviens vers vous.

Un peu d'anglais pour les autres ... Oh sorry I didn't see the insert in the dump ... ok for the examples ... I'll go and do some tests and I say you something soon.

@JeremieB Now I understand why you have difficulties to write your query.

Can you please detail the relationships like this ?

Example :

• a metal has one essai and an essai has one or many metal
• ...

You can also write here how you have declared the relationships in your models.

@vincent15000 ORIGINAL TABLE

	public function prelevable()
	{
		return $this->morphTo();
	}

metal

	public function ORIGINAL_TABLE()
	{
		return $this->morphOne(ORIGINAL::class, 'metal');
	}

	public function essai()
	{
		return $this->belongsTo(Essai::class, 'essai_id');
	}

essai

	public function rapport()
	{
		return $this->belongsTo(Rapport::class, 'idRapport');
	}

@JeremieB https://docs.github.com/en/get-started/writing-on-github/working-with-advanced-formatting/creating-and-highlighting-code-blocks

@JeremieB First of all, you have an error in the relationship in essai ... you don't have any idRapport column in the table, it is rapport_id.

Then you have an Echantillon model, but no Metal model ? Or the table associated with the class Echantillon would be metal ?

@MichalOravec I'm curious ... why do you send this ? I don't understand the link with this post ;).

@vincent15000 Origin he sent code like simple text without code block.

@MichalOravec Ok that's true, I had forgotten ;) ... he has probably updated his posts just now.

@vincent15000 My bad, this error is only with the new SQL table I sent you. I don't have this kind of error in my real table.

I simply make an error when I was redoing my table to show you what I have.

I have updated my previous message with the good model info, so it should answer your question.