logo
down
shadow

NHibernate: Select one to Many Left Join - Take X latest from Parent


NHibernate: Select one to Many Left Join - Take X latest from Parent

By : R Kumar
Date : November 16 2020, 06:23 AM
I hope this helps you . The way I would go here is:
load the list of root entity (Parent) and let NHibernate load their children lazily - in separated SQL query.
code :
session.QueryOver<Parent>()
    .Where(x => x.TimeStamp > from)
    .And(x => x.TimeStamp < to).OrderBy(x => x.TimeStamp).Desc
    //.Left.JoinQueryOver<Child>(x => x.Childs)
    // .TransformUsing(new DistinctRootEntityResultTransformer())
    .Skip(start) // paging
    .Take(maxCapacity)
    .List<Parent>();
<class name="Parent">
    ...
    <bag name="Childs" batch-size="3">
        ...
    </bag>
</class>


Share : facebook icon twitter icon
Select latest row in LEFT JOIN PHP MySQL CodeIgniter

Select latest row in LEFT JOIN PHP MySQL CodeIgniter


By : Clayton
Date : March 29 2020, 07:55 AM
seems to work fine You can use a join condition that only selects the row the maximum id per char_id.
code :
$this->db->select('c.name, e1.exp');
$this->db->from('characters as c');
$this->db->join('experience as e1', 'e1.id = (select max(id) from experience as e2 where e2.char_id = e1.char_id)', 'left');
$this->db->select('c.name, e1.exp');
$this->db->from('characters as c');
$this->db->join('(select max(id) max_id, char_id 
    from experience group by char_id) as t1', 't1.char_id = c.id', 'left')
$this->db->join('experience as e1', 'e1.id = t1.max_id', 'left')
MYSQL: select latest record only (on left join table)

MYSQL: select latest record only (on left join table)


By : carlitos
Date : March 29 2020, 07:55 AM
help you fix your problem I have 2 tables: , You'll need some subquery's for that:
code :
SELECT
    a.*, b.*
FROM
    table1 a
LEFT JOIN
    (SELECT c.id, d.contacted_for, c.time
     FROM
         (SELECT
            id,
            MAX(time) time
         FROM
             table2
         GROUP BY id
         ) c
     JOIN
         table2 d
         ON c.id = d.id AND d.time = c.time
     ) b
     ON a.id = b.id
Fluent NHibernate QueryOver to select items not in another table (left join)

Fluent NHibernate QueryOver to select items not in another table (left join)


By : R. Edward
Date : March 29 2020, 07:55 AM
I hope this helps . It seems to me that it's not possible to create joins without mapped relationships via QueryOver. See this answer.
With subqueries you can do something like this:
code :
All allAlias = null;
var result = Session.QueryOver(() => allAlias)
               .WhereRestrictionOn(x => x.someOtherColumn).IsLike('1')
               .WithSubquery.WhereNotExists(QueryOver.Of<hidden>()
                     .Where(h => h.propA == allAlias.propB || h.propB == allAlias.propB)
                     .Select(h => h.Id))
               .List();
how to select latest DATE in MYSQL LEFT JOIN

how to select latest DATE in MYSQL LEFT JOIN


By : Ali
Date : March 29 2020, 07:55 AM
To fix this issue i have 2 MYSQL tables that i LEFT JOIN based on the machine id column ('mid') , You can add another column and simplify your query allalong:
code :
        SELECT machines.mid as mid
         , machines.machineName as machineName
         , INET6_NTOA(ipAddress) as ipAddress
         , coalesce(count(*), 0) as count 
         , coalesce(MAX(user_reverts.modified),'never') as reverted  -- latest revert 
      FROM machines
 LEFT JOIN user_reverts
        ON ( user_reverts.mid=machines.mid AND user_reverts.uid = 189 )
     WHERE DATE(user_reverts.modified) = CURDATE()
  GROUP BY machines.mid
         ;
Select latest 'N' records on the right table for each record on left table of left join on linq to sql

Select latest 'N' records on the right table for each record on left table of left join on linq to sql


By : user3154147
Date : March 29 2020, 07:55 AM
This might help you I want to select latest 2 records from the right table of left join for each pers_Id on left table in linq. , The following should achieve the desire result.
code :
 var n = 5;

 var query =  from t1 in table1
 join t2 in table2 on t1.pers_Id equals 
 t2.pers_Id into tb
 from t2 in tb.OrderByDescending(x=>x.ORD_CREATETIME).Take(n).DefaultIfEmpty()
 select new {t1,t2}
Related Posts Related Posts :
shadow
Privacy Policy - Terms - Contact Us © ourworld-yourmove.org