Re: Insert/select...

anthony berglas <[email protected]> Tue, 5 Mar 2013 09:13:32 +1000
Newsgroups	gmane.comp.java.orm.simpleorm
Message-ID	<CA+_PZMcGUPAOTr+5zz49O9_fv9tC9UhCtB5YSbxoOEX9G3okxA@mail.gmail.com>
--e89a8fb2054addf17a04d72183a5
Content-Type: text/plain; charset=ISO-8859-1

Cool.  Good that you called it raw.  I suppose multi-row updates and
deletes would be next on this agenda.

I had intended to leave that out of SimpleOrm as you know, but I can see it
is nice to use the OO fields to build the queries.

Anthony

On Mon, Mar 4, 2013 at 8:43 PM, Franck Routier <[email protected]>wrote:

> Hi all,
>
> I have added a new SInsertSelect construct in SimpleORM.
>
> This is a low level tool to generate insert into ... select ... style sql
> based on Metas and SQueryTransient.
> This has the advantage of:
> - using object oriented SRecordMeta and SFieldScalar to build your query
> - delegating the work to the database, which will be the most efficient at
> this task
>
> But keep in mind this is a low level trick, closer to jdbc than to an ORM
> tool.
> In particular:
> - this will bypass any validation done by SRecordInstance.**
> onValidateRecord().
>
>
> So that said, here is how it works... Lets say you have a sales order
> table, and a stats tables.
> You can do the following:
>
> SQueryTransient qryTrs = new SQueryTransient(new
> SQuery<Order>(Order.meta));
> qryTrs.groupBy(Order.**productId).groupBy(Order.**
> month).avg(Order.amount).**selectRaw("nextval('**mysequence')");
>
> SInsertSelect<Stats> insSel = new SInsertSelect(Stats.meta, qryTrs);
> insSel.addField(Stats.**productId).addField(Stats.**month).addField(Stats.
> **averageOrder).addField(Stats.**id);
>
> session.begin();
> session.**rawInsertSelectNoFlush(insSel)**;
> session.commit();
>
>
> Notice the method is called rawInsertSelectNoFlush to reflect the fact
> that this is a low level construct.
> Also notice that the sequence is added using a selectRaw method, and is
> database dependant (the example will work with Postgresql).
>
> Hope this can be useful. Any comment is welcome.
>
> Franck
>
>


-- 

Dr Anthony Berglas, [email protected]       Mobile: +61 4 4838 8874
Just because it is possible to push twigs along the ground with ones nose
does not necessarily mean that that is the best way to collect firewood.

--e89a8fb2054addf17a04d72183a5
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable





<head>

<style type=3D"text/css">
<!--

/* start of attachment style */
       .ygrp-photo-title{
         clear: both;
         font-size: smaller;
         height: 15px;
         overflow: hidden;
         text-align: center;
         width: 75px;
       }
       div.ygrp-photo{
         background-position: center;
         background-repeat: no-repeat;
         background-color: white;
         border: 1px solid black;
         height: 62px;
         width: 62px;
       }

       div.photo-title=20
         a,
         div.photo-title a:active,
         div.photo-title a:hover,
         div.photo-title a:visited {
           text-decoration: none;=20
       }

       div.attach-table div.attach-row {
         clear: both;
       }

       div.attach-table div.attach-row div {
         float: left;
         /* margin: 2px;*/
       }

       p {
         clear: both;
         padding: 15px 0 3px 0;
	 overflow: hidden;
       }

       div.ygrp-file {
         width: 30px;
         valign: middle;
       }
       div.attach-table div.attach-row div div a {
         text-decoration: none;
       }

       div.attach-table div.attach-row div div span {
         font-weight: normal;
       }

       div.ygrp-file-title {
         font-weight: bold;
       }
 /* end of attachment style */
        -->
        </style>
        </head>
<html>
<head>
<style type=3D"text/css">
<!--
#ygrp-mkp {
  border: 1px solid #d8d8d8;
  font-family: Arial;
  margin: 10px 0;
  padding: 0 10px;
}

#ygrp-mkp hr {
  border: 1px solid #d8d8d8;
}

#ygrp-mkp #hd {
  color: #628c2a;
  font-size: 85%;
  font-weight: 700;
  line-height: 122%;
  margin: 10px 0;
}

#ygrp-mkp #ads {
  margin-bottom: 10px;
}

#ygrp-mkp .ad {
  padding: 0 0;
}

#ygrp-mkp .ad p {
  margin: 0;
}

#ygrp-mkp .ad a {
  color: #0000ff;
  text-decoration: none;
}
-->
</style>
</head>
<body>





Cool. =A0Good that you called it raw. =A0I suppose multi-row updates and de=
letes would be next on this agenda.<div><br></div><div>I had intended to le=
ave that out of SimpleOrm as you know, but I can see it is nice to use the =
OO fields to build the queries.</div>

<div><br></div><div>Anthony<br><br><div class=3D"gmail_quote">On Mon, Mar 4=
, 2013 at 8:43 PM, Franck Routier <span dir=3D"ltr">&lt;<a href=3D"mailto:f=
[email protected]" target=3D"_blank">[email protected]</a>&gt;=
</span> wrote:<br>

<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">Hi all,<br>
<br>
I have added a new SInsertSelect construct in SimpleORM.<br>
<br>
This is a low level tool to generate insert into ... select ... style sql b=
ased on Metas and SQueryTransient.<br>
This has the advantage of:<br>
- using object oriented SRecordMeta and SFieldScalar to build your query<br=
>
- delegating the work to the database, which will be the most efficient at =
this task<br>
<br>
But keep in mind this is a low level trick, closer to jdbc than to an ORM t=
ool.<br>
In particular:<br>
- this will bypass any validation done by SRecordInstance.<u></u>onValidate=
Record().<br>
<br>
<br>
So that said, here is how it works... Lets say you have a sales order table=
, and a stats tables.<br>
You can do the following:<br>
<br>
SQueryTransient qryTrs =3D new SQueryTransient(new SQuery&lt;Order&gt;(Orde=
r.meta));<br>
qryTrs.groupBy(Order.<u></u>productId).groupBy(Order.<u></u>month).avg(Orde=
r.amount).<u></u>selectRaw(&quot;nextval(&#39;<u></u>mysequence&#39;)&quot;=
);<br>
<br>
SInsertSelect&lt;Stats&gt; insSel =3D new SInsertSelect(Stats.meta, qryTrs)=
;<br>
insSel.addField(Stats.<u></u>productId).addField(Stats.<u></u>month).addFie=
ld(Stats.<u></u>averageOrder).addField(Stats.<u></u>id);<br>
<br>
session.begin();<br>
session.<u></u>rawInsertSelectNoFlush(insSel)<u></u>;<br>
session.commit();<br>
<br>
<br>
Notice the method is called rawInsertSelectNoFlush to reflect the fact that=
 this is a low level construct.<br>
Also notice that the sequence is added using a selectRaw method, and is dat=
abase dependant (the example will work with Postgresql).<br>
<br>
Hope this can be useful. Any comment is welcome.<span class=3D"HOEnZb"><fon=
t color=3D"#888888"><br>
<br>
Franck<br>
<br>
</font></span></blockquote></div><br><br clear=3D"all"><div><br></div>-- <b=
r><p>
</p><p>Dr Anthony Berglas, <a href=3D"mailto:[email protected]" target=3D=
"_blank">[email protected]</a>=A0=A0=A0=A0=A0=A0 Mobile: +61 4 4838 8874<=
br>
Just because it is possible to push twigs along the ground with ones nose<b=
r>
does not necessarily mean that that is the best way to collect firewood.</p=
>
</div>




<!-- |**|begin egp html banner|**| -->

<br>

=20=20=20=20
=20=20=20=20
<br>

<!-- |**|end egp html banner|**| -->


<div width=3D"1" style=3D"color: white; clear: both;"/>__._,_.___</div>

<!-- Start Recommendations -->
<!-- End Recommendations -->



<!-- |**|begin egp html banner|**| -->

  <img src=3D"http://geo.yahoo.com/serv?s=3D97476590/grpId=3D7360226/grpspI=
d=3D1705006905/msgId=3D2083/stime=3D1362438856" width=3D"1" height=3D"1"> <=
br>

<!-- |**|end egp html banner|**| -->

=20=20
<!-- |**|begin egp html banner|**| -->

<br>
      <div style=3D"font-family: verdana; font-size: 77%; border-top: 1px s=
olid #666; padding: 5px 0;" >
      Your email settings: Individual Email|Traditional <br>
      <a href=3D"http://groups.yahoo.com/group/SimpleORM/join;_ylc=3DX3oDMT=
JmOHY1MWJzBF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlY=
wNmdHIEc2xrA3N0bmdzBHN0aW1lAzEzNjI0Mzg4NTY-">Change settings via the Web</a=
> (Yahoo! ID required) <br>
      Change settings via email: <a href=3D"mailto:SimpleORM-digest@yahoogr=
oups.com?subject=3DEmail Delivery: Digest">Switch delivery to Daily Digest<=
/a> | <a href =3D "mailto:[email protected]?subject=3D=
Change Delivery Format: Fully Featured">Switch to Fully Featured</a> <br>
           <a href=3D"http://groups.yahoo.com/group/SimpleORM;_ylc=3DX3oDMT=
JkamU0NWVtBF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlY=
wNmdHIEc2xrA2hwZgRzdGltZQMxMzYyNDM4ODU2">
        Visit Your Group=20
      </a> |
      <a href=3D"http://docs.yahoo.com/info/terms/">
        Yahoo! Groups Terms of Use
      </a> |
      <a href=3D"mailto:[email protected]?subject=3DUns=
ubscribe">
       Unsubscribe=20
      </a>=20
 <br>
    </div>
  <br>

<!-- |**|end egp html banner|**| -->


<div style=3D"color: white; clear: both;"/>__,_._,___</div>
</body>

</html>

--e89a8fb2054addf17a04d72183a5--