Выбор различных значений из пар ключ/значение в отдельной таблице

У меня есть 2 таблицы базы данных

JOBS(JOB_ID, JOB_TIME, JOB_NAME,...), JOB_PARAMETERS(JOB_ID,NAME,VALUE)

где JOB_PARAMETERS — это, по сути, карта, содержащая пары ключ-значение параметра задания. Каждое задание может иметь уникальные пары параметров ключ/значение.

Я пытаюсь прагматично построить запрос, который будет возвращать разные идентификаторы заданий, содержащие комбинации ключ/значение. Если значения на самом деле представляют собой список значений, операторы сравнения. Например:

JOB_PARAMETERS: NAME = 'OUTPUT_FILENAME', VALUE LIKE "ALEX%", "JAX%"
                NAME = 'PRIORITY' , VALUE > 7

В приведенном выше примере будут автоматически отфильтрованы все задания, у которых нет OUTPUT_FILENAME и ключа PRIORITY. Возврат всех заданий, отвечающих обоим условиям.

Мне также нужно иметь возможность поддерживать нумерацию страниц и порядок.

Я планировал использовать Perl с DBIx::Class, но я могу сделать это и на чистом Perl/SQL.

Я открыт для изменения схемы базы данных, но каждое задание может иметь разные пары ключ/значение, поэтому я не могу просто сделать их столбцами в таблице заданий.

Заранее спасибо.


mysql dbi perl dbix-class
person Smartelf    schedule 19.04.2013    source источник
comment
являются ли имена параметров уникальными для данного задания?   -  person a1ex07    schedule 19.04.2013
comment
да, они должны быть уникальными   -  person Smartelf    schedule 19.04.2013

Ответы (2)


arrow_upward
1
arrow_downward

При использовании DBIx::Class вы можете сгенерировать схему DBIC с помощью Schema::Loader.

После подключения к базе данных вы получаете объект $schema, который можно использовать для фильтрации ResultSet для возврата нужных вам объектов Result:

my $rs_job_parameters = $schema->resultset('Job_Parameters')->search({
    -or => [
        {
            'name' => 'OUTPUT_FILENAME',
            'value' => [{ like => 'ALEX%'}, { like => 'JAX%' }].
        },
        {
            'name' => 'PRIORITY',
            'value' => [{ '>' => 7}].
        }
    ]},
    {
        columns  => [qw( job_id )],
        group_by => [qw( job_id )], # alternative you can use distinct => 1 to group_by all selected columns
        having   => \[ 'COUNT(*) = ?', [ 2 ] ],
    }
);

my @job_ids = $rs_job_parameters->get_column('job_id')->all;
person Alexander Hartmaier    schedule 19.04.2013

arrow_upward
1
arrow_downward

Это можно сделать в SQL, сгруппировав JOB_PARAMETERS по JOB_ID и соответствующим образом отфильтровав группы. Например, если существует ограничение уникальности для (JOB_ID, NAME), можно запросить следующим образом:

SELECT   JOB_ID
FROM     JOB_PARAMETERS
WHERE    (NAME='OUTPUT_FILENAME' AND (VALUE LIKE 'ALEX%' OR VALUE LIKE 'JAX%'))
      OR (NAME='PRIORITY' AND VALUE > 7)
GROUP BY JOB_ID
HAVING   COUNT(*) = 2

В отсутствие такого ограничения уникальности COUNT(*) пришлось бы заменить, например. с COUNT(DISTINCT NAME).

person eggyal    schedule 19.04.2013
comment
Спасибо, обратите внимание, почему вы поместили предложение HAVING COUNT(). У данного задания может быть больше пар ключ/значение, которые просто игнорируются или могут использоваться для order_by. - person Smartelf; 19.04.2013
comment
@Smartelf: без предложения HAVING будут возвращены все группы, соответствующие предложению WHERE (т. е. совпадающие хотя бы по одному параметру); предложение HAVING гарантирует, что возвращаются только те группы, которые содержат два совпадения записей (т. е. совпадения по обоим параметрам). - person eggyal; 19.04.2013