Query on Hasmany Reference

Query on HasMany reference

As almost always, NHibernate does have answer for this. What we are here trying to achieve would be a SQL Statement lookin like this:

// final Request selection
SELECT request.[RequestId] 
 FROM [Request] request 

   // Only requests, which are successful, and have Max(date)
   WHERE request.[RequestId] IN 
   (
     SELECT successResponse.RequestId as y0_ 
      FROM [Response] successResponse 

        // response which max date is equal to the upper response
        // and which RequestId corresponds with supper upper Request
        WHERE EXISTS
        (
          SELECT maxResponse.RequestId as y0_
           , max(maxResponse.[DateTime]) as y1_           
           FROM [Response] maxResponse 

           // do the MAX only for current Request
           WHERE maxResponse.RequestId = successResponse.RequestId 
           GROUP BY maxResponse.RequestId 

           // assure that the Response match is on the max DateTime
           HAVING max(maxResponse.[DateTime]) = successResponse.[DateTime]
        ) 
        AND successResponse.[Success] = 1
   )

Notes:

1. Expecting the Response does have RequestId
2. above was used C# // comment instead of SQL --

And now the magic of NHibernate and QueryOver:

// This declaration will allow us, to use a reference from middle SELECT
// in the most deeper SELECT
Response response = null;

// the most INNER SELECT
var maxSubquery = QueryOver.Of<Response>()
   .SelectList(l => l
    .SelectGroup(item => item.RequestId)
    .SelectMax(item => item.DateTime)
    )
    // WHERE Clause
   .Where(item => item.RequestId == response.RequestId)
   // HAVING Clause
   .Where(Restrictions.EqProperty(
      Projections.Max<Response>(item => item.DateTime),
      Projections.Property(() => response.DateTime)
    ));

// the middle SELECT
var successSubquery = QueryOver.Of<Response>(() => response)
    // to filter the Request
    .Select(res => res.RequestId)
    .WithSubquery
    .WhereExists(maxSubquery)
    // now only these wich are successful
    .Where(success => success.Success == true)
    ;

At this moment we have to inner SUB SELECTs, nested. let's use them:

// the most outer SELECT
var query = session.QueryOver<Request>();
query.WithSubquery
    // our Request ID is IN(...
    .WhereProperty(r => r.ID)
    .In(successSubquery);

var list = query
    .List<Request>();

Final notes, I am not discussing the concept. Not the performance. I would use rather a setting on response "IsActive" and make it easier ... this is just the answer how to do that...

How to access model hasMany Relation with where condition?

Just in case anyone else encounters the same problems.

Note, that relations are required to be camelcase. So in my case available_videos() should have been availableVideos().

You can easily find out investigating the Laravel source:

// Illuminate\Database\Eloquent\Model.php
...
/**
 * Get an attribute from the model.
 *
 * @param  string  $key
 * @return mixed
 */
public function getAttribute($key)
{
    $inAttributes = array_key_exists($key, $this->attributes);

    // If the key references an attribute, we can just go ahead and return the
    // plain attribute value from the model. This allows every attribute to
    // be dynamically accessed through the _get method without accessors.
    if ($inAttributes || $this->hasGetMutator($key))
    {
        return $this->getAttributeValue($key);
    }

    // If the key already exists in the relationships array, it just means the
    // relationship has already been loaded, so we'll just return it out of
    // here because there is no need to query within the relations twice.
    if (array_key_exists($key, $this->relations))
    {
        return $this->relations[$key];
    }

    // If the "attribute" exists as a method on the model, we will just assume
    // it is a relationship and will load and return results from the query
    // and hydrate the relationship's value on the "relationships" array.
    $camelKey = camel_case($key);

    if (method_exists($this, $camelKey))
    {
        return $this->getRelationshipFromMethod($key, $camelKey);
    }
}

This also explains why my code worked, whenever I loaded the data using the load() method before.

Anyway, my example works perfectly okay now, and $model->availableVideos always returns a Collection.

Laravel/Eloquent: querying a belongsTo / hasMany relation

First you should set up your relationships:

class Task extends Model {
    public function executions() {
        return $this->hasMany(Execution::class);
    }
}

class Execution extends Model {
    public function task() {
        return $this->belongsTo(Task::class);
    }
}

Then your query should be:

$task = Task::doesntHave('executions')
            ->orWhereHas('executions', function($query) use ($name) {
                return $query->where('process_name', '<>', $name);
            })
            ->oldest()
            ->first();

Of course you could wrap those queries in a query scope.

Custom hasMany relationship query with select()

Add the filter inside your events method like :

public function events()
{
    return $this->hasMany('App\Event')->select('id', 'company_id', 'name');
}

Then call it like:

Auth::user()->company->events

Important update: 'company_id' is necessary for company->events link, otherwise 'events' relationship always returns empty array

Fetch specific fields on a hasMany relation

Yes, it's possible. You've got a couple options.

*NB: For options 1 and 2 below, the foreign key (user_id) must be selected so that Laravel knows how to link the models together when building the relationships.

1. Modify the relationship query when using it. The with() method can accept an array of key/value pairs where the key is the name of the relationship and the value is a Closure that modifies the relationship query.

    $data = \App\User::with(['articles' => function($query) {
        // user_id is required here*
        $query->select(['id', 'title', 'user_id']);
    }])->get();
   

2. Create a new relationship that contains the fields you want.

    public function articleTitles() {
        // user_id is required here*
        return $this->hasMany('App\Article')->select(['id', 'title', 'user_id']);
    }
   
    $data = \App\User::with('articleTitles')->get();
   

3. If you're only concerned about the array/json output, you can modify the App\Article model to only display the id and title when converted to an array.

    class Article extends Model {
        protected $visible = ['id', 'title'];
    }
   

What you choose depends on what you need.

Complex find query for hasMany relationship in cakePHP 1.3

As you are trying to retrieve data in a hasMany relationship, cakephp doesn't join the tables by default. If you go for joins you can do something like:

$this->paginate = array(
                   'joins'=>array(
                              array(
                               'table'=>'accounts',
                               'alias'=>'Account',
                               'type' =>'inner',
                               'conditions' =>array('User.id = Account.user_id')
                              )
                            ),
                            'conditions'=> array('OR' => 
                               array(
                                'Account.name'=>$this->params['named']['nickname'],
                                'User.id' => 5)
                               )
                            );
$users = $this->paginate();
         $this->set('users',$users);
debug($users);
$this->render('/users/index');

You have to fit this according to your needs of course. More on joins, like already mentioned in another answer.

Edit 1: This is because you are missing the second 'conditions'. See my code snippet. The first 'conditions' just states where the join happens, whereas the second 'conditions' makes the actual selection.

Edit 2: Here some info on how to write conditions in order to select needed data. You may want to use the max function of your rdbms on column created in your refined condition.

Edit 3: Containable and joins should not be used together. Quoted from the manual: Using joins with Containable behavior could lead to some SQL errors (duplicate tables), so you need to use the joins method as an alternative for Containable if your main goal is to perform searches based on related data. Containable is best suited to restricting the amount of related data brought by a find statement. You have not tried my edit 2 yet, I think.

Edit 4: One possible solution could be to add a field last_updated to the table Sites. This field can then be used in the second conditions statement to compare with the SiteMeta.created value.

---

Related Topics