Testing CONCAT queries with SQLite

I am testing my application and have set it up to use a sqlite in-memory database. How would you go about testing the following knowing that the CONCAT doesn't work in sqlite (it's || instead)? Is there a way round this?

<?php

namespace App\Repositories;

use App\Models\Contact;

class Contacts extends EloquentRepository
{
    protected $model;

    function __construct(Contact $model)
    {
        $this->model = $model;
    }

    public function search($term, $orgId = null)
    {
        return $this->model->where(DB::raw(CONCAT('first_name', ' ', 'surname'), 'LIKE', "%$term%")->get();
    }

felixpenrose

5 years ago

Level 3

Did you ever come across a solution to this? I've tried setting the mysql driver to use PIPES_AS_CONCAT so that MySQL and SQLite can both use the same syntax

'mysql' => [
    'driver' => 'mysql',
    ...
    'modes' => [
        'PIPES_AS_CONCAT',
    ]
],

I've then got the following query;

DB::update(
    'UPDATE table
    SET text_box = text_box || :content)
    WHERE id IN(' . implode(',', $ids) . ')'
  , compact('content')
);

However this when running tests with SQLite nulls the data if I try and use the column name (anything other than that using || does work e.g. "test concat " || :content

Then under the MySQL prod database it fails with a syntax error. Very frustrating...

zoidq

5 years ago

Level 7

Did anyone find a solution to how we can use a DB::query() that uses CONCAT for testing with sqlite :inmemory?

Tray2

5 years ago

Level 74

I solved it by creating a database view migration and depending on if it's ran locally or not creates the view for SQLite or MySQL.

local == mysql

<?php

use Illuminate\Database\Migrations\Migration;

class CreateBookViews extends Migration
{
    public function up()
    {
        if (App::environment() == 'local') {
            DB::statement(
                "CREATE OR REPLACE VIEW book_views AS
                 SELECT
                    (SELECT GROUP_CONCAT(a.id ORDER BY a.id SEPARATOR ',')
                     FROM authors a, author_books ab
                     WHERE a.id = ab.author_id
                     AND ab.book_id = b.id) author_id,
                    (SELECT GROUP_CONCAT(concat(a.last_name, ', ', a.first_name)
                     ORDER BY a.last_name, a.first_name SEPARATOR ' & ')
                     FROM authors a, author_books ab
                     WHERE ab.author_id = a.id
                     AND ab.book_id = b.id) author_name,
                     b.id book_id,
                     b.title,
                    (SELECT ROUND(AVG(s.score), 1)
                    FROM scores s
                    WHERE s.item_id = b.id
                    AND media_type_id = 1) rating,
                    b.series,
                    b.part,
                    b.released,
                    g.id genre_id,
                    g.genre,
                    f.id format_id,
                    f.format,
                    CASE series
                        WHEN 'Standalone'
                        THEN b.released
                        ELSE (SELECT MIN(bi.released)
                              FROM books bi
                              WHERE bi.series = b.series)
                        END series_started
                FROM books b,
                     genres g,
                     formats f
                WHERE b.genre_id = g.id
                AND   b.format_id = f.id"
            );
        } else {
            DB::statement(
                "CREATE VIEW IF NOT EXISTS book_views AS
                 SELECT (SELECT GROUP_CONCAT(a.id)
                        FROM authors a, author_books ab
                        WHERE a.id = ab.author_id
                        AND ab.book_id = b.id) author_id,
                        (SELECT GROUP_CONCAT(author_name, ' & ')
                         FROM (SELECT a.last_name || ', ' || a.first_name author_name
                               FROM authors a, author_books ab
                               WHERE ab.author_id = a.id
                               AND ab.book_id = b.id
                               ORDER BY a.last_name, a.first_name)) author_name,
                        b.id book_id,
                        b.title,
                        (SELECT ROUND(AVG(s.score), 1)
                        FROM scores s
                        WHERE s.item_id = b.id
                        AND media_type_id = 1) rating,
                        b.series,
                        b.part,
                        b.released,
                        g.id genre_id,
                        g.genre,
                        f.id format_id,
                        f.format,
                        CASE series
                    	    WHEN 'Standalone'
                            THEN b.released
                    	    ELSE (SELECT MIN(bi.released)
                            FROM books bi
                            WHERE bi.series = b.series)
                       	END series_started
                FROM books  b,
                     genres  g,
                     formats  f
                WHERE b.genre_id = g.id
                AND   b.format_id = f.id"
            );
        }
    }

    public function down()
    {
        DB::statement('DROP VIEW IF EXISTS book_views');
    }
}

1 like

codegreen

1 year ago

Level 13

@zoidq, with some help from AI, I came up with this.

$uniqueIdExpression =
            $driver === 'sqlite'
                ? DB::raw("PREFIX-' || table.id as unique_id")
                : DB::raw('CONCAT("PREFIX-", table.id) as unique_id');

$rows = $model->select('id', 'name', $uniqueIdExpression)->get();

It's not the best solution because it does not solve the issue globally. In my case, it is okay because it's nearly a one-off situation. I'm also aware that this query does not make sense; its for demonstration purposes only. Im also aware that this thread is very old.

Please or to participate in this conversation.