Delphi filter по полю Lookup

Добрый день, дорогие друзья. В одной из своих предыдущих статей я рассматривал механизм фильтрации записей набора данных Delphi с помощью свойства Filter. Вот эта статья.

Сейчас же рассмотрим частный случай такой фильтрации, которая осуществляется по подстановочным lookup-полям набора данных.

Подстановочные lookup-поля очень удобны. Они предоставляют возможность визуально объединить данные из нескольких таблиц базы данных, но при этом остается возможность редактирования таких объединенных данных, когда вы их видите в своем DBGrid. Ведь классически, когда вы просто с помощью команды Select осуществили выборку, объединяющую данные из нескольких таблиц и получили набор данных — вы не можете его редактировать. Хотя я здесь сделаю отступление и скажу, что Delphi это может легок делать. Для этого ему нужно просто задать в наборе данных имя уникальной таблицы, которая будет непосредственно редактироваться. Но это другая тема. Сейчас же мы рассматриваем подстановочные поля и как можно добиться фильтрации по ним.

Пример с lookup-полем

Итак, давайте перейдем к примеру. Сначала мы посмотрим на наши таблицы из базы данных.

Конечно это фрагмент базы данных, но сейчас нам нужен только он. Здесь представлены три таблицы, которые связаны между собой связями. Ну если быть точнее, то таблица КВР и КОСГУ связаны между собой связью много-ко-многим. Для этого используется третья таблица КВР_КОСГУ, объединяющая в себе первичные ключи таблиц КВР и КОСГУ. О типах связи вы можете узнать, прочитав статью «Виды связей между таблицами».

КВР и КОСГУ это термины бухгалтерского учета, но мы сейчас не будем их рассматривать. Я думаю, что в рамках этой статьи нам это не нужно. Давайте лучше посмотрим на нашу форму, которая связана с этими тремя таблицами.

Эту форму мы уже рассматривали в статье «Фильтрация набора данных в Delphi с помощью свойства Filter», но тогда мы не рассматривали фильтрацию по подстановочному полю.

Давайте посмотрим на эту форму. В ней есть три таблицы DBGrid. Верхний DBGrid у нас выводит данные из таблицы КВР. Средний DBGrid — из КОСГУ, а нижний из третьей таблицы — КВР_КОСГУ.

И вот нижний DBGrid подключен к набору данных, который, если смотреть на картинку нашей формы, то можно сказать, что он выводит данные, используя команду Select, объединяющую в себе три таблицы. Но это не так. Для нижнего DBGrid используется три отдельных набора данных. Один adoKVRKOSGU, извлекающий данные из таблицы КВР_КОСГУ. В нем достаточно простой запрос для извлечения данных:

SELECT
    квр_косгу.Код_КВР_КОСГУ,
    квр_косгу.Код_КВР,
    квр_косгу.Код_КОСГУ,
    квр_косгу.Статус
FROM квр_косгу

А два других набора данных являются подстановочными. Если вы посмотрите на запрос, который я только что привел, то в нем нет полей КВР и КОСГУ, есть только поля, которые являются внешними ключами (Код_КВР и Код_КОСГУ). Но, если мы посмотрим на форму, то в форме то представлены не коды, а именно поля КВР и КОСГУ, являющиеся номерами статей, а также их названия.

Так вот вспомогательные наборы данных выводят нам информацию о КВР и КОСГУ из справочников КВР и КОСГУ соответственно. У набора данных adoKVRL (так я называю подстановочные наборы данных, добавляя к ним префикс L в конце. Так удобнее) запрос на выборку будет выглядеть следующим образом:

SELECT
    квр.Код_КВР,
    квр.Статья AS Статья_КВР,
    квр.Название AS Название_КВР
FROM квр

Аналогично у набора данных adoKOSGUL выборка извлекается запросом:

SELECT
    косгу.Код_КОСГУ,
    косгу.Статья AS Статья_КОСГУ,
    косгу.Название AS Название_КОСГУ
FROM косгу 

Как видите, в форме мы используем эти самые поля. Как создавать подстановочные поля в наборе данных описывать не будут — в интернете миллионы статей по этому поводу. Просто хочу сказать, что в наборе данных adoKVRKOSGU эти поля имеются, не смотря на то, что их нет в запросе.

Теперь смотрим внимательно на нашу форму на нижний DBGrid. В нем из таблицы КВР_КОСГУ есть только одно поле, которое называется Статус. Все остальные поля являются подстановочными lookup полями.

Теперь рассмотрим механизмы фильтрации по этим подстановочным полям. Мне знакомы как минимум два таких способа.

Фильтрация по полю lookup с помощью свойства Filter

Как отфильтровать набор данных по полю Название КВР в нижнем DBGridВ? Это поле у нас чисто подстановочное и мы не можем в применить Filter или оператор WHERE в запросе напрямую. Не прокатит, потому что этого поля нет в наборе данных adoKVRKOSGU.

Так как же быть? Все очень и очень просто. Алгоритм такого поиска следующий:

1. Фильтруем подстановочный набор данных adoKVRL по полю Статья. Это поле соответствует колонке Название КВР нижнего DBGrid;
2. Полученные в результате фильтра значения первичных ключей набора <strong>adoKVRL</strong> используем для фильтрации набора данных <strong>adoKVRKOSGU</strong>. В этом наборе данных эти ключи присутствуют, только они являются здесь не первичными, а внешними. Ну собственно, и все. Это весь алгоритм, а теперь к коду:

procedure TfrmKVRandKOSGU.txtCaptionKVR2Change(Sender: TObject);
var
   sKodKVR: string;
   sFilter: string; //Формируемая строка фильтрации
begin
     if TEdit(Sender).Text<>'' then
     begin
        //Фильтруем подстановочный набор данных adoKVRL по полю Статья (в подстановочном наборе она называется Название_КВР)
        //В этом наборе данных у нас также есть и первичные ключи.
        adoKVRL.Filtered:=false;
        adoKVRL.Filter:='Название_КВР '+ ' LIKE ' + #39 + '%' + TEdit(Sender).Text + '%' + #39;
        adoKVRL.Filtered:=true;
        //После этого у нас получаются выбранные записи и мы будем использовать первичные ключи по полю Код_КВР
        //этих найденных записей для уже непосредственной фильтрации.
        //Для этого мы сформируем строку фильтра в цикле
        sFilter:='';
        adoKVRL.First;
        while not adoKVRL.Eof do
        begin
             sKodKVR:=inttostr(adoKVRL.FieldByName('Код_КВР').AsInteger);
             sFilter:=sFilter+' or '+'Код_КВР ='+sKodKVR;
             adoKVRL.Next;
        end;
        Delete(sFilter, 1, 4); //Удаляем с начала строки ' or '
        adoKVRKOSGU.Filtered:=false;
        adoKVRKOSGU.Filter:=sFilter;
        adoKVRKOSGU.Filtered:=true;
     end else
     begin
        adoKVRL.Filtered:=false;
        adoKVRKOSGU.Filtered:=false;
     end;
end;

В данном случае фильтрация повешена не на кнопку, а на событие onChange поля ввода Edit. Просто так удобно. Когда поле пустое, то фильтр набора данных отключен, когда поле заполнено, то набор данных автоматически фильтруется в соответствии с введенным значением.

Для формирования строки фильтра я использовал цикл, в котором я перебрал все записи, чтобы собрать значения первичных ключей. Вы можете использовать какой-то другой механизм, например, объект Tstrings. Но не суть. Я думаю, что что вы поняли, что смысл в том, чтобы сначала отфильтровать lookup-набор, затем извлечь из полученных записей все первичные ключи и использовать их в качестве фильтра для требуемого набора данных.

Это был один способ. Есть еще второй. Но он уже организуется это уже не фильтрация с помощью свойства Filter, а выборка с помощью команды SELECT.

Фильтрация по полю lookup с помощью команды Select

Различные механизмы выборки с помощью команды Select рассматриваются мною в статье «SQL — запросы к базе данных». Здесь мы будем продолжать нашу тему выборки, используя для этого поле lookup.

В данном случае уже не удобно вешать процедуру отбора записей на событие onChange, потому что в предыдущем примере набор данных фильтровался. То есть, все значения из таблицы уже были получены и те записи, которые не соответствовали условию фильтра лишь прятались. Поэтому на скорость это не влияло.

При команде SELECT такое навряд ли прокатит. Нет, может быть и прокатит, если записей в базе данных не очень много. Потому что, представьте, вы ввели, букву и сразу набор данных закрылся, осуществилась новая выборка, набор открылся. Только вы ввели новый символ, снова тоже самое. Если наборы большие, то это будет занимать время, поэтому в данном случае весь поиск лучше повесить на кнопку.

Давайте теперь посмотрим на другой пример и на другую форму. До этого у нас было три таблицы. Давайте теперь рассмотрим более полную структуру этой базы данных:

По нашим исходным данным мы видим, для каждой связки КВР_КОСГУ существует несколько записей с видами затрат, а каждая запись с видом затрат содержится несколько раз в плане (для каждого календарного года). Здесь на рисунке отсутствует таблица Года и другие, чтобы не делать расфокусировку внимания на другие детали.

Теперь посмотрим на форму:

Здесь нас будет интересовать только нижний DBGrid. Скажем так, что здесь выборка организована также несколькими наборами данных, включая lookup-наборы, как и в предыдущем случае.

Давайте сразу посмотрим на код, а потом ниже прокомментируем его. Мне кажется, что так мне легче будет его описать.

procedure TfrmZayavki.cmdFilterClick(Sender: TObject);
var
     sFilter, sFilterSQL: string; //Переменная для поиска
begin
     adoZayavki.Close;
     with adoZayavki do
     begin
          sql.Clear;
          sql.add('SELECT');
          sql.add('заявки.Код_заявки,');
          sql.add('заявки.Код_плановой_суммы,');
          sql.add('заявки.Код_года,');
          sql.add('заявки.Номер,');
          sql.add('заявки.Дата,');
          sql.add('заявки.Назначение_платежа,');
          sql.add('заявки.Сумма,');
          sql.add('заявки.Статус');
          sql.add('FROM Заявки');
     end;
     if cmdFilter.Caption='Фильтр' then
     begin
         if rbKVR.Checked then
         begin
            sFilter:=trim('%'+txtFilter.Text+'%');
            sFilterSQL:='WHERE Код_плановой_суммы IN '+
                        '(SELECT Код_плановой_суммы FROM план WHERE Код_затраты IN '+
                              '(SELECT Код_затраты FROM виды_затрат WHERE Код_КВР_КОСГУ IN'+
                                    '(SELECT Код_КВР_КОСГУ FROM КВР_КОСГУ WHERE Код_КВР IN'+
                                          '(SELECT Код_КВР FROM КВР WHERE квр.Статья LIKE '''+sFilter+'''))))';
         end;
         sFilterSQL:=sFilterSQL+' and Код_года=:Код_года';
         adoZayavki.sql.add(sFilterSQL);
         cmdFilter.Caption:='Отменить фильтр';
         //adoZayavki.sql.add('ORDER BY Номер ASC');//показывает, что недостаточно сведений о ключевом поле
         adoZayavki.Open;
         adoZayavki.Sort:='Номер ASC'; //вместо orde by
     end else
     begin
         cmdFilter.Caption:='Фильтр';
         adoZayavki.sql.add('WHERE Код_года=:Код_года');
         adoZayavki.sql.add('ORDER BY Номер ASC');
         adoZayavki.Open;
     end;
end;

Смотрите, сначала мы по основному набору данных делаем выборку, но не включаем выражение WHERE.

Затем мы проверяем, если флажок По КВР установлен, то мы добавляем предложение WHERE. И вот в нем все самое интересное. Как бы это сказать, мы делаем структуру из вложенных запросов, в каждом из которых мы переходим к левой таблице, которая находится по отношению к текущей.

Затем мы полученный фильтр добавляем к общему запросу с помощью adoZayavki.sql.add (sFilterSQL);

Обратите внимание, что для сортировки набора данных я не стал использовать adoZayavki.sql.add ('ORDER BY Номер ASC'); Не знаю почему, но в этой конструкции это не всегда работает и приводит к ошибке: «Недостаточные или неполные сведения о ключевом поле». Обычно эта ошибка появляется, когда в наборе отсутствуют первичные ключи, но в моем случае они есть. Может быть на это влияет большой уровень вложенности (при небольшом такого не происходит). Хотя логически я понимаю, что такого не должно быть. На факт. Может быть драйвер доступа к данным криво написан, Бог его знает. Вылетает ошибка и я решил заменить эту строку вот этой: «adoZayavki.Sort:='Номер ASC';», воспользовавшись возможностями по сортировке набора данных.

Так что, вот вам два варианта того, как можно организовать фильтрацию в Delphi по полям lookup.