WhereRaw for Dates in d365

[lbarron-md]

Hi,

Thanks for this amazing library, however I'm having problems trying to filter a query using a date field in D365. They way this accepts is is $filter=<date_field> lt 2021-01-12T14:21:02Z. As you can see the date value is unescaped but the grammar always surrounds the value with single quotes if this is not a boolean.

Is there any workaround or fix for this?

[Joost-Wolthuis]

Anyone has the answer?

[lbarron-md]

Hey @Joost-Wolthuis, I ended up creating my own dummy client. Notice that is very laravely, but I hope you can figure it out

    public function getEntity(string $entity, array $filters = [], $count = 0)
    {
        $nextLink = null;
        $responseValues = [];
        do{
            $headers  = $this->getDefaultHeaders(); # Gets the auth token

            $endpoint = $this->config->resource . '/data/'. $entity;

            $top          = $count ? '&$top=' . $count : '' ;
            $queryString  = '?$filter=1 eq 1 ';
            $queryString .= implode(' ', $filters);
            $endpoint    .= $queryString . $top;

            // Override if there is a nextLink which means it already got requested
            $endpoint = $nextLink ?? $endpoint;

            $response = Http::withHeaders($headers)->get($endpoint); # You can replace it with guzzle

            $arrayResponse = $response->json();

            array_push($responseValues, ...($arrayResponse['value'] ?? []));

        }while($nextLink = $arrayResponse['@odata.nextLink'] ?? false);

        return $responseValues;
    }
# Usage
        $config        = (object) Config($this->apiConfigPath);
        $client        = new ApiClient($config);

        $zuluStartDate = $this->getStartDate()->toIso8601ZuluString();
        $zuluEndDate   = $this->getEndDate()->toIso8601ZuluString();
        $filters = [
            "and RecordModifiedDateTime ge {$zuluStartDate}",
            "and RecordModifiedDateTime lt {$zuluEndDate}",
        ];

        $payload = $client->getEntity('YourEntity', $filters);

Hope it helps

[KieranFJ]

I implemented internally a 'whereRaw()' method that simply takes a manually built string and adds it as a $filter. Intended for some edgecases that the library might not have support for immediately.

Might be of use?

https://github.com/KieranFJ/odata-client-php/commit/73715f65584df4813ba7c4f3f480e82f51a892ba

Basically you would just need to do something like

$whereRaw = "Quantity add QuantityUsed gt 0";   
$builder->whereRaw($whereRaw); 

If appropriate i could create a pull request.

[ThaDaVos]

As they are extending Laravel's Query Builder, why not use the DB::raw function? I just tried it and it works