Hey all,
I am having a bit of an issue getting filtering to work with a FreeformStatementDelegate backed sql container…
The issue is, the generated query is wrapping my column in quotes inside the upper (in the where clause). This is a no-go for Postgres at least.
The generated SQL looks like this, which will not work:
SELECT ... FROM table1 c
LEFT JOIN table2 c2
ON c2.id= c.parent_id
INNER JOIN table3 ct
ON ct.type_id= c.type_id
WHERE UPPER("c2.name") LIKE '%ROBERT%' LIMIT 20 OFFSET 0
The where clause could be like this:
WHERE UPPER(c2.name)
or like this:
WHERE UPPER(c2."name")
and the query would run fine with correct results.
I set my filter like this:
Filter f = new SimpleStringFilter("c2.name", "ROBERT", true, false);
sq.addContainerFilter(f);
I cannot seem to figure out a different way to set the filter which will work, as I need to use the alias, or it is an ambiguous column name due to the self join.
Surely someone has run into this before? Any help would be appreciated.
Well, I ended up coming up with a workaround by re-implementing all of the filters and their transformations without the calls to QueryBuilder.quote(); to wrap columns in the where clause in double quotes. It works, but is very inelegant to me.
Is this the intended behavior for those filters?
Question for anyone knowledgable about that area of the code… why is it doing that? Is that valid for MySQL? Because I know for Postgres and SQL Server (where I have the most experience), it would not be. That limits you to only being able to support a very basic schema with no overlapping column names in your joins, and no self joins, since you can’t use an alias for the column in your filter.
Let me know if there is anything else I can provide to help you better understand if necessary.
seems to me that there’s just no way for the SQLContainer to know that there’s an alias or join used when the names are quoted for the filters. The FilterTranslators don’t have any metadata to work with at that point, so they only have the propertyId from the Filter object which is basically a String.
I’ve just tripped over this with MySQL and FreeformStatementDelegate, but with a much simpler scenario - a single Equal filter.
What puzzles me is why standard TableQueries and Filters work, but not FreeFormQueries - the queries on the wire to MySQL look identical, with the column names double-quoted.
Hi Jon,
So, i’ll prefix this by saying databases are actually my normal day job, java dev is just a hobby for me.
It looks like your problem is how MySQL syntax differs from other database vendors. For Postgres you can wrap a column name in “column_name” for this effect, for Sql Server, you can use either “column_name” or [column_name]
. With MySQL by default, that character is column_name.
The setting you changed brings MySQL in line with pretty much every other database I have used. Why they insist on being different I have no idea. There are so many “gotchya’s” every time I have to use MySQL, where as I can pick up most other DB’s without too many things working differently than I would expect.
Now why it was working with a table query, but not a FreeformStatementDelegate, I have no idea… if the query going over to the DB was the same, it should either break or work on both.
Can you take that query from your MySQL log, and try and run it in MySQL Workbench or something similar, and see if it works?
My issue all has to do with using table alias’. Using quotes tells the database “this is exactly what the column name is”. So if you quote the table alias too, you run into issues because you now no longer have a column name which matches something in your database.
Looking at the full MySQL log, Vaadin issues a ’ SET SESSION sql_mode = ‘ANSI’ ’ at the beginning of the session, which is why things I was testing in isolation in Workbench were not working… Rebuilding the SQL in my Delegate, things seem to be consistent again.
Hello. I know this is a very old question, but I am facing exactly the same problem. Was this ever solved? In my case, it fails in the method getCountStatement() of my DerivedQueryDelegate implements FreeformStatementDelegate. I rewrote with getCountStatement as follows
public StatementHelper getCountStatement()
throws UnsupportedOperationException {
StatementHelper sh = new StatementHelper();
StringBuffer query = new StringBuffer(“SELECT COUNT(*) " + fromtext);
StringBuffer where = new StringBuffer(”");
if (filters != null) {
where.append(QueryBuilder.getWhereStringForFilters(
filters, sh));
where the effect of the whereString.replace statement is replacing all the instances of my aliased columns e.g.
select a.name description from asset a join assetype at on a.assettype_id = at.id
there I would replace the string “desciption” by a.name. The bottom line, the value of the query variable would be a valid MySQL statement (except for the ?) it would look like select a.name description from asset a join assettype at on a.assettpye_id = at.id where upper(a.name) like ?. However, when it enters the FreeformQuery class (getResults method) the statement has been transformed into select a.name description from asset a join assettype at on a.assettpye_id = at.id where upper(name) like ?. How come? Where does it happen that I lose the alias? At any rate, that is not so important, the important question to me is if there is a solution for this already or what sort of workaround would be acceptable. I am using MySql. Thanks very much in advance.
Just wanted to say that my solution, although not very elegant, worked in the end. I simply forgot to add the same sql statement substitution to the getQueryStatement method of the the DerivedQueryDelegate class
Hello! I know this might be oudated but I´m having the same issue.
I use the SQLContainer add-on to access a mysql database, but the data I query is not a just simple table but a quite complex query with lots of inner joins. I also use the FilteringTable add-on.
I implemented the FreeformStatementDelegate interface for a custom FreeformQueryDelegate for my purpose
To generate the main table that I want to display I have a query like this:
SELECT a.id AS ID, a.customer_name AS CUSTOMER, o.name as ORDER
LEFT JOIN orders o ON a.order_id = .id
LEFT JOIN …
So, I am having about eight inner joins in my principle query, which works fine, and I get my table even showing the correct column captions (ID, CUSTOMER, ORDER etc…) as in the main query.
But I am facing exactly the same problem when filtering (using FitleringTable). the resulting queries are not matching my complex join szenario. For example, When I filter the column CUSTOMER (table column as a result of my inner join, but not a real existent column in database), it looks like this: … WHERE UPPER(“CUSTOMER”) LIKE ?
I have come to a solution implementing all the FiltersTransaltors and the QueryBuilder myself, as commented Adam Brusselback at first (removing the QueryBuilder.quote calls and the UPPER(“”) parts). And also I did as Eduardo Alonso came up with, renaming the table colums from the table filter and replacing my real sql condition (e.g. CUSTOMER replaced by a.customer_name). So finally my queries are generated just fine.
I know there are other approaches besides the SQLContainer add on, like the LazyQueryContainer or possible using the JPACOntainer,.
If anyone has any idea what´s the best approach for this szenario I would be very thankful. Or whether this is the right way to do it.
My solution does work fine now. Would be great to hear whether this issues has been solved or not.