How to check if a date (dd/mm/yyyy) has been submitted?

user_7079188 0
Viewed 239

I have a filter bar where you can search with names, and vehicule brand but I wanted to know how to actually check with dates in this format -> dd/mm/yyyy as right know nothing happens when I search a date, I receive a blank page without errors, but just zero results.

I found maybe a solution using this $fullDate = DateTime::createFromFormat('d/m/Y',$filter); but i have a struggle where to add it in my code.

So I will share you my code and what I researched

The query where you can see all queries with the filter arguments (for my search)

public function getQueryByTypeAndPro($type, User $user, $archive, $filter)
    {

        $fullDate = \DateTime::createFromFormat('d/m/Y', $filter);
        $qb = $this->createQueryBuilder("opn");

        $or = new Orx();

        if($fullDate !== false) {
            $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.dateCreation', ':filter'));
        } else {
            $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.id', ':filter'));
            $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.vehiculeMarque', ':filter'));
            $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.clientNom', ':filter'));
            $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.clientPrenom', ':filter'));
        }

        $or->addMultiple($textFilters);
        $qb->andWhere($or);

        $query = $qb
            ->andWhere("opn.type = :type")
            ->andWhere("opn.resellerId = :reseller")
            ->andWhere("opn.archive = :archive")
            ->setParameters([
                "type" => $type,
                "reseller" => $user->getId(),
                'archive' => $archive,
                'filter' => '%' . $filter . '%'
            ])
            ->orderBy("opn.dateCreation", "DESC")
            ->getQuery()
        ;

        return $query;
    }

This is what it shows me in my query profiler when I do a date search in this format dd/mm/yyyy 

SELECT 
  count(r0_.id) AS sclr0 
FROM 
  reseller_operation r0_ 
WHERE 
  r0_.dateCreation LIKE ? 
  AND r0_.type = ? 
  AND r0_.reseller_id = ? 
  AND r0_.archive = ?

So right now at that moment this code work in a way that when I do a dump dump($fullDate) it shows false when I don't enter a date, and shows the date when I do. So I guess that does work at the moment. The problem i still have is that it doesn't show any results, just a blank page with zero results.

I don't know if you need my controller or my twig to get a better picture? I didn't put them in here because I thought this wouldn't be necessary, but feel free to ask me.

symfony filter sql
2 Answers

You are doing fine. In your Repository, you can check if filter is a date time or a string and design your query accordingly.

createFromFormat will return false, If it doesn't get a correct date format from the string provided, we can take advantage from it.

I have edited your code from what I understand : 

 public function getQueryByTypeAndPro($type, User $user, $archive, $filter)
{
    $fullDate  = \DateTime::createFromFormat('d/m/Y',$filter);


    $qb = $this->createQueryBuilder("opn");

    $or = new Orx();

    $params = [
        "type" => $type,
        "reseller" => $user->getId(),
        'archive' => $archive,
        'filter' => '%' . $filter . '%'
    ];

    // Filter date only if the filter is a valid date.
    if ($fullDate !== false) {
        $date = $fullDate->format('Y-m-d');
        $textFilters[] = $qb->expr()->andX($qb->expr()->eq('opn.dateCreation', ':date_filter'));
        $params['date_filter'] = $date;
    }
    else {
        // Filter considerting filter as a name or anything else.
        $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.id', ':filter'));
        $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.vehiculeMarque', ':filter'));
        $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.clientNom', ':filter'));
        $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.clientPrenom', ':filter'));
    }

    $or->addMultiple($textFilters);
    $qb->andWhere($or);

    $query = $qb
        ->andWhere("opn.type = :type")
        ->andWhere("opn.resellerId = :reseller")
        ->andWhere("opn.archive = :archive")
        ->setParameters($params)
        ->orderBy("opn.dateCreation", "DESC")
        ->getQuery()
    ;

    return $query;
}

Does it make sense?

user_11403580

Ok I succeeded in making that work. Here is the code.

public function getQueryByTypeAndPro($type, User $user, $archive, $filter)
    {

        $qb = $this->createQueryBuilder("opn");

        $fullDate = \DateTime::createFromFormat( "d/m/Y", $filter);


        $or = new Orx();

        if($fullDate !== false) {
            $fullDate = $fullDate->format('Y-m-d');
            $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.dateCreation', ':date'));
        }

        $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.id', ':filter'));
        $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.vehiculeMarque', ':filter'));
        $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.clientNom', ':filter'));
        $textFilters[] = $qb->expr()->orx($qb->expr()->like('opn.clientPrenom', ':filter'));

        $or->addMultiple($textFilters);
        $qb->andWhere($or)
            ->andWhere("opn.type = :type")
            ->andWhere("opn.resellerId = :reseller")
            ->andWhere("opn.archive = :archive")
            ->setParameters([
                "type" => $type,
                "reseller" => $user->getId(),
                'archive' => $archive,
                'filter' => '%' . $filter . '%'
            ])
            ->orderBy("opn.dateCreation", "DESC");

        if ($fullDate) {
            $qb->setParameter('date','%'. $fullDate .'%');
        }

        return $qb->getQuery();
    }

Hope that helps some in the same situation

user_70791880
Related