add a complex select expression plus its alias programmatically

Excellent,
I understand better views now.
Thank you.

Now I have another question, suppose I want to add some condition so that i can filter using a HAVING CLAUSE, for example,

SELECT
CASE MailAddress
WHEN 'AdPro1' then AddressPro1Line1
WHEN 'AdPro2' then AddressPro2Line1
WHEN 'AdPer1' then AddressPer1Line1
WHEN 'AdPer2' then AddressPer2Line2
ELSE 'Unknown Address'
END AS DeliverAddressLine1
FROM TABLE
HAVING DeliverAddressLine1 LIKE '%blabla%'

The reason I'd like to do that is one can not use an ALIAS in a WHERE clause in SQL.

It seems that filter are always pushed to WHERE clause (Am i wrong here ?).

Question :
Is it possible to implement this HAVING filter by extending
views_handler_filter_string (and overiding the some methods) or should I look for a workaround ?

Thanks again for your help,

Kenjil

Still not fixed for the first problem,

I extended the class query of views_handler_field by overriding query method :

  function query() {
    $this->ensure_my_table();
    $this->field_alias = $this->query->add_field(
      'table',
      "CASE MailAddress
            WHEN 'AdPro1' then AddressPro1Line1
            WHEN 'AdPro2' then AddressPro2Line1
            WHEN 'AdPer1' then AddressPer1Line1
            WHEN 'AdPer2' then AddressPer2Line2
      ELSE 'Unknown Address'",
      'DeliveryAddressLine1');
  }

This doesn't works. It happens that when views api is constructing the query, it preffixes with 'Table.' the complex select expression ending up in a SQL error (here Table.CASE doesn't make sense):

SELECT blabla,
      Table.CASE MailAddress
            WHEN 'AdPro1' then AddressPro1Line1
            WHEN 'AdPro2' then AddressPro2Line1
            WHEN 'AdPer1' then AddressPer1Line1
            WHEN 'AdPer2' then AddressPer2Line2
      ELSE 'Unknown Address' AS DeliveryAddress
      FROM TABLE WHERE blabla

So i'm asking myself, did i choose the wrong way or is views2 missing an add_complex_select_expression which would add a 'complex select expression' without adding the 'Table.' as prefix as it is now defined in method views_query::query ?

Thanks again, Kenjil

Fixed. If table field is '' when calling add_field, no table name is prefixed.

For the filter side, I gave up the idea of using a having clause which creates SQL problem where mixed with ORDER BY clause. I use add_where with reformulating my complex select statement instead of using its alias.

Everything works like a charm.

Thanks, Kenji

For the person who wants to see the code, here is how to extend views_handler_filter_string :

class views_handler_filter_string_ville extends views_handler_filter_string {
	
	/**
   * Add this filter to the query.
   *
   * Due to the nature of fapi, the value and the operator have an unintended
   * level of indirection. You will find them in $this->operator
   * and $this->value respectively.
   */
  function query() {
    $this->ensure_my_table();

    $upper = $this->case_transform();
    // after set of $upper
    $complex_expression = "CASE
					WHEN `smfrecords`.`AdresseCourrier` = 'AdPro1' then $upper(`smfrecords`.`AdPro1Ville`)
					WHEN `smfrecords`.`AdresseCourrier` = 'AdPro2' then $upper(`smfrecords`.`AdPro2Ville`)
					WHEN `smfrecords`.`AdresseCourrier` = 'AdPer1' then $upper(`smfrecords`.`AdPer1Ville`)
					WHEN `smfrecords`.`AdresseCourrier` = 'AdPer2' then $upper(`smfrecords`.`AdPer2Ville`)
					END";

    $info = $this->operators();
    if (!empty($info[$this->operator]['method'])) {
      $this->{$info[$this->operator]['method']}($complex_expression, $upper);
    }
  }  
}