SQL artisan multi-layer query condition nesting function.

in actual development, we often query or count subtable information, it is still relatively simple to process a layer. If the Layer 2, Layer 3, or more, the written conditions are complicated and prone to write errors. SQL artisan In addition to flexible comparison functions, the Field object , Object in method also provides n layer condition nesting function, it makes it more flexible to construct query conditions.

ToMSSQLOfNorthwindFor example:

Query the sales amount of a specified employee in a certain period of time.

Processed table:Employees, orders, order details

During the implementation process, you can use table join queries or directly query the target table. to directly query the target table, you must add the conditions of the parent table to the current query conditions in a nested manner.

Employee. Employee-> orders. orderdate-> sum (Order details)As a reverse condition nesting.

the following two query methods are available in SQL statement and SQL artisa implementation under the component.

(CodeOnly used to expressSQL ArtisanComponent functions,SQLOther issues of the statement itself are not discussed)

SQLMethod 1:

Select sum (unitprice * quantity * (1-discount) from employees inner join orders on employees. employeeid = orders. employeeid inner join [Order Details] on orders. orderid = [Order Details]. orderidWhere (employees. employeeid = 3 and orders. orderdate> = '2017-7-1 ')And orders. orderdate <= '2017-8-1 ')

 

SQL ArtisanMethod 1:

Query = session. createquery (employees. TBL. Inner (orders. TBL, orders. _ employeeid )&

Orders. TBL. Inner (orderdetails. TBL, orders. _ orderid ));

Query. Selects =NewFieldadapter [] {sqlmath. sum (orderdetails. _ unitprice * orderdetails. _ quantity * (1-orderdetails. _ discount ))};

Query. expreesion = employees. _ employeeid. At (employees. TBL) = 3 &

Orders. _ orderdate. At (orders. TBL)> = datetime. parse ("1997-7-1")&

Orders. _ orderdate. At (orders. TBL) <= datetime. parse ("1997-8-1");

DS = query. executedataset ();

SQLMethod 2:

Select sum (unitprice * quantity * (1-discount) from [Order Details]Where orderid in (select orderid from orders where (orderdate> = '2014-7-1 'and orderdate <'2014-8-1 ')And employeeid in (select employeeid from employees where (employeeid = 3 )))))

 

SQL ArtisanMethod 2:

IQUERY query = session. createquery (orderdetails. TBL );

Query. Selects =NewFieldadapter [] {sqlmath. sum (orderdetails. _ unitprice * orderdetails. _ quantity * (1-orderdetails. _ discount ))};

Query. expreesion = orderdetails. _ orderid. In (orders. TBL, orders. _ orderid,

Orders. _ orderdate> = datetime. parse ("1997-7-1")&

Orders. _ orderdate <datetime. parse ("1997-8-1")&

Orders. _ employeeid. In (employees. TBL, employees. _ employeeid, employees. _ employeeid = 3 ));

System. Data. dataset DS = query. executedataset ();

 

from the above code, you can see SQL artisan the component uses the in the method is convenient and flexible to nest multi-layer conditions, use strings to spell out SQL the statement is not a nightmare for developers, but it is also a headache for developers to check their legality; in addition, it is often necessary to face more complex query conditions.

SQL ArtisanIt brings you a fully object-oriented conditional compiling method and provides compilation detection in the development environment, which greatly improves the efficiency and quality of conditional compiling.

ActuallySQLTheSQLAll statements are run throughSQL ArtisanThe.

download the example