Problems with quick search and advanced search

Scriptcase 8 Discussion
, ,
#1

Hi,
I’ve a grid with the next query:


SELECT
    c.id AS idcc,
    c.nombreComercial AS cc_name,
    nc.id AS idc,
    nc.id_cliente AS idu,
    u.nombre AS cli_name,
    u.apellidos AS cli_surname,
    nc.vigencia AS active,
FROM_UNIXTIME(nc.fechaAlta) as dateFrom,
FROM_UNIXTIME(nc.fecha_limite) as dateExpire,
    nc.tipoDocumento,
    nc.documento,
    nc.tipoContrato AS type,
    nc.empresaSeguridad,
    nc.codigoPromo,
    nc.collectiveId,
    ac.collective_name
FROM 
    cras c INNER JOIN nectar_contratos nc ON c.id=nc.cra INNER JOIN usuarios u ON nc.id_cliente=u.id LEFT JOIN app_collectives ac ON nc.collectiveId=ac.collective_id

This query is not very complex, but the problem is that when I perform a search or quick search, the where clausule is never executed with the search input, by example, I type “Fernando” that is a name that appear in cli_name field, and the query result is:


(pdo-mysql): SELECT nc.id as idc, c.id as idcc, c.nombreComercial as cc_name, nc.id_cliente as idu, u.nombre as cli_name, u.apellidos as cli_surname, nc.documento as nc_documento, nc.vigencia as active, nc.tipoContrato as type, ac.collective_name as ac_collective_name, FROM_UNIXTIME(nc.fechaAlta) as datefrom, FROM_UNIXTIME(nc.fecha_limite) as dateexpire, nc.codigoPromo as nc_codigopromo from cras c INNER JOIN nectar_contratos nc ON c.id=nc.cra INNER JOIN usuarios u ON nc.id_cliente=u.id LEFT JOIN app_collectives ac ON nc.collectiveId=ac.collective_id where ( 1 <> 1 )AND c.id = 1 LIMIT 0,12

Then this means that the search doesn’t work.
Do you know why this happens and how to solve this?
Thanks,

Dani

#2

Usually I solve this kind of issues by moving the SQL statement from the SC app to a new db view.

So that in the SC app you can use a very simple:

select field1, field2, .... from view_name
#3

I imagine this could be a solution, but I’d like to prescind of use any views in my code. This means that is a scriptcase bug?

#4

You said you like to “prescind of use any views in my code”. Why?

#5

I don’t want to use use sql views because is comlex to keep updated and our admin doesn’t allow us to use them.