Обсуждение: How to limit dropdown list to options based on 'in use' rows from lookup table, for webpage using php and postgresql

On 29/12/2015 16:23, Killian Driscoll wrote:
> I have a table country with a lookup table to country_type.
>
> table country
>
> country_id (pk), col2, col3, country_type_id (fk) integer
>
> lookup table
>
> country_type country_type_id (pk), type_name varchar
>
> My lookup table has 50 country types, but only 20 are 'in use' in the
> current db (with the rest possibly used later as the db expands). On the
> webpage I am creating (I am using a php generator) I can set it up to
> add a filter by country type based on the country type_name varchar - so
> one can view the country types by name rather than the id integer - but
> all 50 types are listed.
>
> I only want the 20 that currently 'in use' (not sure if this is the best
> way to describe it) and therefore relevant to searches/filters (i.e. I
> don't want to see a "There are no records to display" message).

How do you define "in use"? Is it a switch turned on and off by an
admin? is it based on how often that country is chosen by a user? Maybe
something else?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 29/12/2015 16:23, Killian Driscoll wrote:
> I have a table country with a lookup table to country_type.
>
> table country
>
> country_id (pk), col2, col3, country_type_id (fk) integer
>
> lookup table
>
> country_type country_type_id (pk), type_name varchar
>
> My lookup table has 50 country types, but only 20 are 'in use' in the
> current db (with the rest possibly used later as the db expands). On the
> webpage I am creating (I am using a php generator) I can set it up to
> add a filter by country type based on the country type_name varchar - so
> one can view the country types by name rather than the id integer - but
> all 50 types are listed.
>
> I only want the 20 that currently 'in use' (not sure if this is the best
> way to describe it) and therefore relevant to searches/filters (i.e. I
> don't want to see a "There are no records to display" message).

How do you define "in use"? Is it a switch turned on and off by an
admin? is it based on how often that country is chosen by a user? Maybe
something else?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 29/12/2015 16:47, Killian Driscoll wrote:
> Hi,
> I posted my reply to the pgsql-php mailing list but in the meantime I
> got an email to say I was removed from the list: I just joined it so I
> don't know why I was removed....

Hi Killian,

Odd - I'll post this to the list also so others will see it.

> Anyhow, below is the reply I tried to post:
>
> Apologies - 'in use' is my grasping at describing it, and as I guessed
> it was unclear. By 'in use' I mean that from my 50 lookup options for
> county types, to date in the db only 20 country types have been used,
> i.e. my db's countries are of 20 types, e.g. country 1 is type
> 'glorious', country 2 is type 'barren', but no country so far is type
> 'lucky': when one uses the webpage filter I only want the type lookups
> used to date to be seen, i.e. 'glorious' or 'barren', but not 'lucky' as
> the latter would result in a "There are no records to display" message.

Ah, OK, I understand now. I'd imagine a simple inner join between the
two tables should do the trick - you'll get only those rows which exist
in both tables. Something like this:

select [whatever]
from country_table ct
inner join lookup_table lt on (ct.country_type_id = lt.country_type_id)
[etc]

Is this what you need?

Hope the weather's a bit better in Dublin than it is here in Galway -
we're getting lashed by Frank at the moment. :-)

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 29/12/2015 17:08, Killian Driscoll wrote:
> While I fix getting kicked off the list I'll reply directly to you if
> that's OK!

No hassle.

<snip>

> Not sure - the php generator I am using states that to create a filter
> condition:
> "Filter condition allows you to reduce the list of values represented in
> the lookup editor with a specified criterion. This condition corresponds
> to the WHERE clause applied to the data source (you must not add the
> WHERE keyword to beginning of the condition)."
>
> So, I need a WHERE condition based on my tables.

How about moving the join into a subquery using EXISTS? - like this:

[where] exists (
  select 1
  from  country_table ct inner join lookup_table lt
    on (ct.country_type_id = lt.country_type_id)
)


Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 29/12/2015 17:24, Killian Driscoll wrote:
> So it should be:
> exists select [whatever]
> from country_table ct
> inner join lookup_table lt on (ct.country_type_id = lt.country_type_id)
> [etc]
>
> [whatever] [etc] - what are these?

Apologies - [whatever] stood for whatever columns you might want to
return from the query, for example:

  select country_id, col2, col3, [...] from ....

In the case of the subquery inside EXISTS, however, it doesn't matter
what you return - all EXISTS tests is whether or not any rows are
returned. In this instance, it's common simply to write

  select 1 from ....

The [etc] stood for any clauses that might follow the WHERE clause, such
as ORDER BY. You don't need ORDER BY in the EXISTS test, as again all
you're checking is whether or not any rows are returned - it doesn't
matter what order their in.

So, short version, what you probably need is just:

exists select 1 from country_table ct
inner join lookup_table lt on (ct.country_type_id = lt.country_type_id)

Hope this helps,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 29/12/2015 18:28, Killian Driscoll wrote:

> Not sure what went wrong, but there's an error: here is the php produced
> by the generator: the data show surveypoints instead of countries, but
> using the same lookup principle:
>
> $lookupDataset = new TableDataset(
>                 new PgConnectionFactory(),
>                 GetConnectionOptions(),
>                 '"irll"."surveypoint_type"');
>             $field = new IntegerField('surveypoint_type_id', null, null,
> true);
>             $field->SetIsNotNull(true);
>             $lookupDataset->AddField($field, true);
>             $field = new StringField('surveypoint_type_name');
>             $lookupDataset->AddField($field, false);
>             $field = new StringField('surveypoint_type_description');
>             $lookupDataset->AddField($field, false);
>             $lookupDataset->setOrderByField('surveypoint_type_name',
> GetOrderTypeAsSQL(otAscending));
>
> $lookupDataset->AddCustomCondition(EnvVariablesUtils::EvaluateVariableTemplate($this->GetColumnVariableContainer(),
> 'exists select 1 from surveypoint ct
>             inner join surveypoint_type lt on (ct.surveypoint_type_id =
> lt.surveypoint_type_id)'));
>
>
$this->AdvancedSearchControl->AddSearchColumn($this->AdvancedSearchControl->CreateLookupSearchInput('surveypoint_type_id',
> $this->RenderText('Survey point Type'), $lookupDataset,
> 'surveypoint_type_id', 'surveypoint_type_name', false, 0));

I'm afraid I don't know whatever framework you're using. Have you any
way of getting it to generate the actual SQL query?

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 30/12/2015 08:41, Killian Driscoll wrote:
> On 29 December 2015 at 23:48, Raymond O'Donnell <rod@iol.ie
> <mailto:rod@iol.ie>> wrote:
>
>     On 29/12/2015 19:33, Killian Driscoll wrote:
>
>     >     I'm afraid I don't know whatever framework you're using. Have you any
>     >     way of getting it to generate the actual SQL query?
>     >
>     >
>     > No, it's a php generator, so my only option is to put in the WHERE
>     > clause in the option box provided, but from the produced php, it looks
>     > like it's going in the wrong option box.....
>
>     Hmm, afraid I can't help you with that, so, as I don't know what you're
>     using.
>
>
> I'm using Maestro php generator (I have v. little experience with
> postgresql or php: I'm an archaeologist....).
>
> As a possible workaround, I have created a view with a DISTINCT ON that
> gives the rows from the lookup table that are used to date, and then in
> the php generator I can link the lookup table's type_id to that view
> when building the filter. It works, but I'm concerned that this may be
> 'messy' or bad practice: any thoughts?

Well, you're always going to be limited by whatever the generator does,
so it really comes down to tricking it into giving you what you want -
so in this case I wouldn't worry about messiness as long as the results
are right. :-)

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie