Problem with Search

[getNo]

I have a Problem with Search.
the Browse Select:

Code: Select all

SELECT ts.Erstellt,
Anfrage_Data.*,
tk.Anfragenummer_intern,
tk.Angebotswert,
tk.Angebotsnummer
FROM Anfrage_Data 
join (
    	Select Anfrage_Historie.Anfrage_ID,
    		Anfrage_Historie.Person,
    		Anfrage_Historie.Status,
    		Anfrage_Historie.Firma,
    		Anfrage_Yes.Anfragenummer_intern,
    		Anfrage_Yes.Angebotswert,
    		Anfrage_Yes.Angebotsnummer
        from Anfrage_Historie
            left join Anfrage_Yes
            on Anfrage_Historie.ID = Anfrage_Yes.Anfrage_His_ID
    	where Anfrage_Historie.Firma IN
    		(SELECT Supervisor_Firmen.SupTo 
    		from Supervisor_Firmen 
    		WHERE Supervisor_Firmen.SupFrom IN 
    			(SELECT Person_in_Firma.Firma FROM Person_in_Firma WHERE Person_in_Firma.Person ='#USER_ID#'
                    )
            )
    	GROUP by Anfrage_Historie.Anfrage_ID
    	) tk
    on Anfrage_Data.ID = tk.Anfrage_ID
left join (
    Select Anfrage_Historie.Timestamp as Erstellt, Anfrage_Historie.Anfrage_ID as tsAnfrage_ID
    from Anfrage_Historie
    where Anfrage_Historie.Status='E'
    GROUP by Anfrage_Historie.Anfrage_ID) ts
on Anfrage_Data.ID = ts.tsAnfrage_ID
where 1=1

I hoped that the last where clause could be the point where the search is hooking in, but no.

With Search "Sachsen" i get the following wrong SQL.

Code: Select all

SELECT anfrage_id,
       id,
       erstellt,
       deadline,
       sachbearbeiter,
       kunde,
       anfragetyp,
       anfragetext,
       anfragenummer,
       angebotswert,
       angebotsnummer,
       ansprechpartner
FROM   anfrage_data
       JOIN (SELECT anfrage_historie.anfrage_id,
                    anfrage_historie.person,
                    anfrage_historie.status,
                    anfrage_historie.firma,
                    anfrage_yes.anfragenummer_intern,
                    anfrage_yes.angebotswert,
                    anfrage_yes.angebotsnummer
             FROM   anfrage_historie
                    LEFT JOIN anfrage_yes
                           ON anfrage_historie.id = anfrage_yes.anfrage_his_id
             WHERE  anfrage_historie.firma IN (SELECT supervisor_firmen.supto
                                               FROM   supervisor_firmen
                                               WHERE
                    supervisor_firmen.supfrom IN (SELECT
                    person_in_firma.firma
                                                  FROM
                    person_in_firma
                                                  WHERE
                    person_in_firma.person = '60225676c60c933'))
             GROUP  BY anfrage_historie.anfrage_id) tk
         ON anfrage_data.id = tk.anfrage_id
       LEFT JOIN (SELECT anfrage_historie.timestamp  AS Erstellt,
                         anfrage_historie.anfrage_id AS tsAnfrage_ID
                  FROM   anfrage_historie
                  WHERE  anfrage_historie.status = 'E'
                         AND (( CONVERT(id USING utf8) LIKE "%sachsen%"
                                 OR CONVERT(erstellt USING utf8) LIKE
                                    "%sachsen%"
                                 OR CONVERT(deadline USING utf8) LIKE
                                    "%sachsen%"
                                 OR CONVERT(sachbearbeiter USING utf8) LIKE
                                    "%sachsen%"
                                 OR CONVERT(kunde USING utf8) LIKE "%sachsen%"
                                 OR CONVERT(anfragetyp USING utf8) LIKE
                                    "%sachsen%"
                                 OR CONVERT(anfragetext USING utf8) LIKE
                                    "%sachsen%"
                                 OR CONVERT(anfragenummer USING utf8) LIKE
                                    "%sachsen%"
                                 OR CONVERT(angebotswert USING utf8) LIKE
                                    "%sachsen%"
                                 OR CONVERT(angebotsnummer USING utf8) LIKE
                                    "%sachsen%"
                                 OR CONVERT(ansprechpartner USING utf8) LIKE
                                    "%sachsen%" ))
                  GROUP  BY anfrage_historie.anfrage_id) ts
              ON anfrage_data.id = ts.tsanfrage_id
WHERE  1 = 1
LIMIT  0, 20 

How can i fix it easily to get the searching fields on the end in the last where clause?

[Janusz]

Hi, not sure if the same case but I have some problems with search if the multiple conditions in the WHERE statement were not in brackets.
So in my case the first real example was not properly working with search but the second one was fine:

Code: Select all

SELECT * FROM actions2
WHERE act_id4created_by='#USER_ID#' OR act_id4updated_by='#USER_ID#' OR act_responsible='#USER_ID#'

SELECT * FROM actions2
WHERE (act_id4created_by='#USER_ID#' OR act_id4updated_by='#USER_ID#' OR act_responsible='#USER_ID#')

[getNo]

Hey Janusz,
thanks for your advice.

Adding some Brackets didnt fix it.

Code: Select all

WHERE  (1 = 1)

[kev1n]

nuBuilder can't handle complex queries like that, especially when there's more than one WHERE clause.

But you have 2 possibilities to workaround it:

1. Move the complex SQL to a view
2. Use a temporary table (e.g. see here)

[getNo]

Hello Kevin,
thx for the fast reply as usual.

can i maybe edit the search code to add the searching conditions on the end of the query or the last where clause?

This would be much easier for me, then your workarounds on all my forms

[kev1n]

can i maybe edit the search code to add the searching conditions on the end of the query or the last where clause?

Sure, you can try that.

[getNo]

can i maybe edit the search code to add the searching conditions on the end of the query or the last where clause?

Sure, you can try that.

Could you give me a hint, where to looking for ?

[kev1n]

Sometimes I use a SELECT ( FROM SELECT ... ) construct like:

Code: Select all

SELECT *
FROM
  (SELECT *
   FROM your_table
   LEFT JOIN other_table ON your_table_id = other_table_id) T
WHERE (conditions here ...)
ORDER BY some_column

[getNo]

i tried, but it still injects the search in the wrong where clause.

Code: Select all

SELECT id,
       id,
       erstellt,
       deadline,
       sachbearbeiter,
       kunde,
       anfragetyp,
       anfragetext,
       anfragenummer,
       angebotswert,
       angebotsnummer,
       ansprechpartner
FROM   (SELECT ts.erstellt,
               anfrage_data.*,
               tk.anfragenummer_intern,
               tk.angebotswert,
               tk.angebotsnummer
        FROM   anfrage_data
               JOIN (SELECT anfrage_historie.anfrage_id,
                            anfrage_historie.person,
                            anfrage_historie.status,
                            anfrage_historie.firma,
                            anfrage_yes.anfragenummer_intern,
                            anfrage_yes.angebotswert,
                            anfrage_yes.angebotsnummer
                     FROM   anfrage_historie
                            LEFT JOIN anfrage_yes
                                   ON anfrage_historie.id =
                                      anfrage_yes.anfrage_his_id
                     WHERE  anfrage_historie.firma IN (SELECT
                            supervisor_firmen.supto
                                                       FROM   supervisor_firmen
                                                       WHERE
                            supervisor_firmen.supfrom IN (SELECT
                            person_in_firma.firma
                                                          FROM
                            person_in_firma
                                                          WHERE
                            person_in_firma.person = '6051b5c34642c86'))
                     GROUP  BY anfrage_historie.anfrage_id) tk
                 ON anfrage_data.id = tk.anfrage_id
               LEFT JOIN (SELECT anfrage_historie.timestamp  AS Erstellt,
                                 anfrage_historie.anfrage_id AS tsAnfrage_ID
                          FROM   anfrage_historie
                          WHERE  anfrage_historie.status = 'E'
                                 AND (( CONVERT(id USING utf8) LIKE "%sachsen%"
                                         OR CONVERT(erstellt USING utf8) LIKE
                                            "%sachsen%"
                                         OR CONVERT(deadline USING utf8) LIKE
                                            "%sachsen%"
                                         OR CONVERT(sachbearbeiter USING utf8)
                                            LIKE
                                            "%sachsen%"
                                         OR CONVERT(kunde USING utf8) LIKE
                                            "%sachsen%"
                                         OR CONVERT(anfragetyp USING utf8) LIKE
                                            "%sachsen%"
                                         OR CONVERT(anfragetext USING utf8) LIKE
                                            "%sachsen%"
                                         OR CONVERT(anfragenummer USING utf8)
                                            LIKE
                                            "%sachsen%"
                                         OR CONVERT(angebotswert USING utf8)
                                            LIKE
                                            "%sachsen%"
                                         OR CONVERT(angebotsnummer USING utf8)
                                            LIKE
                                            "%sachsen%"
                                         OR CONVERT(ansprechpartner USING utf8)
                                            LIKE
                                            "%sachsen%" ))
                          GROUP  BY anfrage_historie.anfrage_id) ts
                      ON anfrage_data.id = ts.tsanfrage_id) ta
WHERE  ( 1 = 1 ) 

[kev1n]

Then you will probably have to do it with a temporary table or view.