[FIXED] How should I set up a Rails app so that some Objects are linked to others (belong_to) but not all Objects have this relationship?

Issue

I'm not sure whether I've accurately reflected my aim in the title, but I'll explain more here.

In my app I have Companies, and Companies has_many Key_Contacts.

Companies also has_many Sales_Opportunities.

I would like the user to be able to select some of the Key_Contacts that belong_to the Company and associate them with a specific Sales_Opportunity. I would also like the user to be able to add a Key_Contact that is not associated with any Sales_Opportunity.

The aim for this is that I can show the specific Key_Contacts that are involved in one Sales_Opportunity view on the Sales_Opportunity page, but not all of them.

Is it as simple as adding a sales_opportunity_id to the Key_Contacts model, but not setting up the "belongs_to" and "has_many" relationships? Or is there a more "official Rails" method to achieve my goal?

Solution

If I am reading this right, then all you need to do is add another has_many :key_contacts relation to your SalesOpportunity model (and belongs_to :sales_opportunity in your KeyContacts model). Then relate all contacts belonging to a specific sales opportunity.

Answered By - Aventuris

Answer Checked By - Clifford M. (PHPFixing Volunteer)

Read More

[FIXED] Why does ActiveRecord has_many use delete_all instead of destroy_all?

Issue

I have a model which has many children. I was setting/removing the children as such:

mymodel.children_ids = [1,2,3]
mymodel.save #add the children
mymodel.children_ids = [1]
mymodel.save #remove children 2,3

This works just fine, but I just realized that none of the callbacks (i.e. after_destroy) are not being called on the children model.

After some digging, it turns out that the delete_all function is being executed, rather than destroy_all. As the docs correctly state, the delete_all function does not fire off the callbacks, so is there anyway to change this behavior?

Thanks.

Solution

For those interested, I added the following monkeypatch to force the has_many through to perform a destroy_all, rather than delete_all. There might be a better way, so I'm open to suggestions.

module ActiveRecord 
  module Associations
    class HasManyThroughAssociation < HasManyAssociation       
      def delete_records(records)
        klass = @reflection.through_reflection.klass
        records.each do |associate|
          klass.destroy_all(construct_join_attributes(associate)) #force auditing by using destroy_all rather than delete all
        end
      end
    end
  end
end

Answered By - gmoniey

Answer Checked By - David Goodson (PHPFixing Volunteer)

Read More

[FIXED] How to check if an item belongs to a hasMany association before updating in CakePHP 3.2

Issue

What I am trying to do:

I have Estimates and Estimates have items "EstimateItems". When updating a Estimate the EstimateItems changed should update. (using patchEntity)

This is working with my current code, my only problem is that other users can edit the Estimate Items of other users when changing the primary key of a EstimateItem in the edit form, because when patching the existing EstimateItems CakePHP only looks at the primary key of the EstimateItem and doesn't take the association in consideration. Also it's still possible to edit the estimate_id of a EstimateItem while $protected estimate_id is set to false.

So what I need is CakePHP to validate that this EstimateItem belongs to the current association before updating or while trying to update.

I hope some one can tell me what I am doing wrong or what I am missing.

Current Query

UPDATE 
  estimate_items 
SET 
  data = 'Test Query 1', 
  amount = 123456789, 
  tax_id = 3 
WHERE 
  id = 3

Expected Query

UPDATE 
  estimate_items 
SET 
  data = 'Test Query 1', 
  amount = 123456789, 
  tax_id = 3 
WHERE 
  id = 3 AND estimate_id = 1

Current code:

Estimates -> Edit.ctp

<?php $this->Form->templates($formTemplates['default']); ?>
<?= $this->Form->create($estimate, ['enctype' => 'multipart/form-data']) ?>
    <fieldset>
        <legend><?= __('Offerte') ?></legend>

        <?= $this->Form->input('reference', ['label' => __('#Referentie'), 'autocomplete' => 'off']) ?>
        <?= $this->Form->input('client_id',
            [
                'type' => 'select',
                'empty' => true,
                'label' => __('Klant'),
                'options' => $clients
            ]
        )
        ?>

        <?php

        foreach($estimate->estimate_items as $key => $item){
        ?>
        <div class="item">
            <legend>Item</legend>
            <?= $this->Form->hidden('estimate_items.'. $key .'.id') ?>
            <?= $this->Form->input('estimate_items.'. $key .'.data', ['type' => 'text', 'label' => __('Beschrijving')]) ?>
            <?= $this->Form->input('estimate_items.'. $key .'.amount', ['type' => 'text', 'label' => __('Bedrag'), 'class' => 'input-date']) ?>
            <?= $this->Form->input('estimate_items.'. $key .'.tax_id',
                [
                    'type' => 'select',
                    'empty' => true,
                    'label' => __('Belasting type'),
                    'options' => $taxes
                ]
            )
            ?>
        </div>
        <?php
        }

        ?>

        <legend>Informatie</legend>
        <?= $this->Form->input('date', ['type' => 'text', 'label' => __('Offerte datum'), 'autocomplete' => 'off']) ?>
        <?= $this->Form->input('expiration', ['type' => 'text', 'label' => __('Verloop datum'), 'autocomplete' => 'off']) ?>
   </fieldset>
<?= $this->Form->button(__('Save')); ?>
<?= $this->Form->end() ?>

Estimates Controller

namespace App\Controller;

use App\Controller\AppController;
use Cake\Event\Event;
use Cake\ORM\TableRegistry;

class EstimatesController extends AppController
{
public function edit($id){
        $associated = ['EstimateItems'];

        $estimate = $this->Estimates->get($id, ['contain' => $associated]);

        $this->log($estimate);
        if($this->request->is(['patch', 'post', 'put'])) {

            $estimate = $this->Estimates->patchEntity($estimate, $this->request->data, [
                'associated' => $associated
            ]);

            $estimate->total = '0';
            $this->log($estimate);
            $this->log($this->request->data);

            if($this->Estimates->save($estimate, ['associated' => $associated])){
                $this->Flash->success(__('De offerte is bijgewerkt'));
                return $this->redirect(['action' => 'index']);
            }
        }

        $this->set('taxes', $this->Estimates->Taxes->find('list', [ 'keyField' => 'id', 'valueField' => 'tax_name' ]));
        $this->set('clients', $this->Estimates->Clients->find('list', [ 'keyField' => 'id', 'valueField' => 'companyname' ]));
        $this->set('estimate', $estimate);
    }
}

EstimatesTable

<?php
namespace App\Model\Table;

use Cake\ORM\Query;
use Cake\ORM\Table;
use Cake\Validation\Validator;
use Cake\ORM\RulesChecker;
use Cake\ORM\Rule\IsUnique;

class EstimatesTable extends Table
{
public function initialize(array $config)
    {
        $this->addAssociations([
            'hasOne' => ['Taxes'],
            'belongsTo' => ['Companies', 'Clients'],
            'hasMany' => ['EstimateItems' => [
                'foreignKey' => 'estimate_id'
            ]]
        ]);

    }

public function buildRules(RulesChecker $rules){

        // A Node however should in addition also always reference a Site.
       $rules->add($rules->existsIn(['estimate_id'], 'EstimateItems'));

        return $rules;
    }

}

EstimateItem Entity

<?php
namespace App\Model\Entity;

use Cake\ORM\Entity;

class EstimateItem extends Entity
{
    protected $_accessible = [
        '*' => false,
        'data' => true,
        'amount' => true,
        'tax_id' => true,
        'unit_id' => true
    ];
}

EstimateItemsTable

<?php
namespace App\Model\Table;

use Cake\ORM\Entity;
use Cake\ORM\Table;
use Cake\Validation\Validator;
use Cake\ORM\RulesChecker;
use Cake\ORM\Rule\IsUnique;
use Cake\ORM\Query;


class EstimateItemsTable extends Table
{

    public function initialize(array $config)
    {
      $this->addAssociations([
            'belongsTo' => ['Estimates' => ['foreignKey' => 'estimate_id']],
            'hasOne' => ['Taxes' => ['foreignKey' => 'tax_id']]
        ]);
    }

Estimate Entity

<?php
namespace App\Model\Entity;

use Cake\ORM\Entity;

class Estimate extends Entity
{

    /**
     * Fields that can be mass assigned using newEntity() or patchEntity().
     *
     * Note that when '*' is set to true, this allows all unspecified fields to
     * be mass assigned. For security purposes, it is advised to set '*' to false
     * (or remove it), and explicitly make individual fields accessible as needed.
     *
     * @var array
     */
    protected $_accessible = [
        '*' => false,
        'id' => false,
    ];
}

Solution

Markstory Replied to me on github with a solution credits to him: https://github.com/cakephp/cakephp/issues/9527

In Model/Table/EstimateItemsTable.php

---

<?php
namespace App\Model\Table;

use Cake\ORM\RulesChecker;
....
class EstimateItemsTable extends Table
{
....
public function buildRules(RulesChecker $rules){
        $rules->addUpdate(function($entity) {

          if (!$entity->dirty('estimate_id')) {
            return true;
          }
          return $entity->estimate_id == $entity->getOriginal('estimate_id');
        }, 'ownership', ['errorField' => 'estimate_id']);

        return $rules;
    }
}

Answered By - R. Jordaan

Answer Checked By - David Marino (PHPFixing Volunteer)

Read More

[FIXED] When I add has_many, some data is suddently not saved

Issue

I have 3 models, Challenge, Pun, User (managed by Clearance gem)

A User can create a Challenge. A Challenge contains many puns. A User can also create a Pun.

Everything is fine until I set a Pun to belong_to a User, then suddenly Puns are no longer saved.

class User < ApplicationRecord
  include Clearance::User
  has_many :challenges
  has_many :puns
end

class Challenge < ApplicationRecord
  has_many :puns, :dependent => :delete_all
  belongs_to :user
end

class Pun < ApplicationRecord
  belongs_to :challenge
  belongs_to :user
end

In my PunController I have tried to establish the current_user id

  def create
    @pun = @challenge.puns.create(pun_params)
    @pun.user_id = current_user.id if current_user
    redirect_to @challenge
  end

  private

  def set_challenge
    @challenge = Challenge.find(params[:challenge_id])
  end

  def pun_params
    params[:pun].permit(:pun_text,:user_id)
  end

What am I doing wrong? I'm trying to keep it as simple as possible, but seems like Users don't want to be associated with more than one thing, particularly if nested. Is this a Clearance issue?

DB setup:

  create_table "challenges", force: :cascade do |t|
    t.text "title"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.datetime "start_time"
    t.datetime "end_time"
    t.bigint "user_id"
    t.index ["user_id"], name: "index_challenges_on_user_id"
  end

  create_table "puns", force: :cascade do |t|
    t.text "pun_text"
    t.bigint "challenge_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.bigint "user_id"
    t.index ["challenge_id"], name: "index_puns_on_challenge_id"
    t.index ["user_id"], name: "index_puns_on_user_id"
  end

  create_table "users", force: :cascade do |t|
    t.string "name"
    t.string "email"
    t.string "tagline"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.string "encrypted_password", limit: 128
    t.string "confirmation_token", limit: 128
    t.string "remember_token", limit: 128
    t.index ["email"], name: "index_users_on_email"
    t.index ["remember_token"], name: "index_users_on_remember_token"
  end

Solution

Well in you currrent code you don't save user_id after setting it. And if you do not expect creation to fail you can do "create!". So you can try:

def create
  @challenge.puns.create!(pun_params.merge(user_id: current_user.id))

  redirect_to @challenge
end

Answered By - Alexander Sysuiev

Answer Checked By - Marilyn (PHPFixing Volunteer)

Read More

[FIXED] How to specify the key format (i.e. camelcase) for an ActiveModel::Serializer :has_many association as a one-off option (not global config)?

Issue

I have a serializer like this:

class FooSerializer < ActiveModel::Serializer
  attributes :id, :name

  has_many :foo_bars, serializer: BarSerializer
end

class BarSerializer < ActiveModel::Serializer
  attributes :id, :acronym
end

My issue is that when instantiating the serializer and calling as_json on it, I get the following:

$ foo = Foo.first
$ s = FooSerializer.new(foo)
$ s.as_json

$ => {
    :foo => {
        :id => 1,
        :name => "Foo",
        :foo_bars => [
            {
              :id => 1,
              :acronym => "F1",
            },
            {
              :id => 2,
              :acronym => "F2",
            },
        ]
    }
}

But my front end API expects to receive camelcase fooBars rather than snake case foo_bars. How can I configure the serializer to output the foo_bars association with the key fooBars

Solution

(posted this because I figured this out myself, but couldn't find the answer anywhere and hope this helps someone else, or even myself when I inevitably google this again someday...)

Pretty easy to do. Just add the key option to your serializer's has_many

class FooSerializer < ActiveModel::Serializer
  attributes :id, :name

  has_many :foo_bars, serializer: BarSerializer, key: :fooBars
end

Done. Now your serializer will output the has_many with fooBars instead of foo_bars

Answered By - some_guy

Answer Checked By - Marie Seifert (PHPFixing Admin)

Read More

[FIXED] How to correctly apply a has_many relationship with an order by in Ruby on Rails 6

Issue

I am creating a User model in Rails 6 to mirror a model that exists in a separate project. There is a has_many relationship that is causing some problems.

class User < ApplicationRecord
      has_many :activation_histories, inverse_of: :user , => { order "created_at  DESC"} 
end

The project I am basing this on used Rails 3.2 and worked successfully like this

class User < ApplicationRecord
       has_many :activation_histories, inverse_of: :user, order: "created_at desc" 
end

I can see from the official documentation the example using an order by looks as so

class Author < ApplicationRecord
  has_many :books, -> { order "date_confirmed DESC" }
end

I get an error that it is expecting '=>' rather than '->' when I run it as so, but when I use '=>' I am getting

app/models/user.rb:6: syntax error, unexpected =>
app/models/user.rb:6: syntax error, unexpected '}', expecting `end'
app/models/user.rb:6: syntax error, unexpected =>
app/models/user.rb:6: syntax error, unexpected '}', expecting `end'
app/models/user.rb:6: syntax error, unexpected =>
app/models/user.rb:6: syntax error, unexpected '}', expecting `end'

I am relatively new to Ruby on Rails and am not sure where I am going wrong here or how to proceed. Removing the inverse_of has no effect on the errors I am seeing.

Any advice on how to correctly use this would be appreciated.

Solution

Try changing

has_many :activation_histories, inverse_of: :user , -> { order "created_at  DESC"} 

to

has_many :activation_histories, -> { order "created_at  DESC"} , inverse_of: :user

Your scope should be your second argument. https://www.rubydoc.info/docs/rails/4.1.7/ActiveRecord%2FAssociations%2FClassMethods:has_many

Answered By - RHFS

Answer Checked By - Clifford M. (PHPFixing Volunteer)

Read More

[FIXED] When will ActiveRecord save associations?

Issue

1. I know that it will save associations when autosave: true as per https://api.rubyonrails.org/classes/ActiveRecord/AutosaveAssociation.html

2. I know that it will save associations that are constructed like

book = Book.new(name: 'foo')
book.authors.build(name: 'bar') #has_many
book.save

or like

book = Book.new(name: 'foo')
book.build_author(name: 'bar') #has_one
book.save

3. I think associations are also saved when they are assigned or added

book = Book.new(name: 'foo')
book.author = Author.new(name: 'bar')
book.save

or

book = Book.new(name: 'foo')
book.authors << Author.new(name: 'bar')
book.save

But, I have a complicated bug that involves something not auto-saving when I would expect it to. So, I want to debug by inspecting book to verify what I think is going to be saved will actually be saved.

TL; DR; What internal state is checked when saving associations? I'm assuming that a model has an internal instance variable like associations_to_save that associations get added to when they are created. Then, when the model is saved, it loops through those associations and saves them too.

Solution

Unfortunately there are no such thing like associations_to_save. However there are some rules saying what is being saved when. You can find those here: http://guides.rubyonrails.org/association_basics.html. Points: 4.1.5 (belongs_to), 4.2.5 (has_one), 4.3.4 (has_many) and 4.4.4 (habtm).

UPDATE:

In case of has_many association, the child is saved on saving the parent if child.new_record? returns true (child was not yet saved to db), or the foreign_key column needs to be updated. This is why:

1. Adding object to association on saved parent do save new child.
2. Adding object to association on unsaved parent doesn't save (no foreign key value)
3. If unsaved parent is being saved and has some child objects in association cache, those objects are saved to update foreign_key.

Answered By - BroiSatse

Answer Checked By - Robin (PHPFixing Admin)

Read More

[FIXED] How to paginate associated records?

Issue

Products belongsToMany Categories and Categories hasMany Products, inside my Product view I'm showing a list of all it's categories but I want to paginate or limit these results.

My current code on ProductsController is:

$product = $this->Products
    ->findBySlug($slug_prod)
    ->contain(['Metas', 'Attachments', 'Categories'])
    ->first();

$this->set(compact('product'));

I know I need to set $this->paginate() to paginate something but I can't get it working to paginate the categories inside the product. I hope you guys can understand me.

UPDATE: Currently I have this going on:

$product = $this->Products->findBySlug($slug_prod)->contain([
              'Metas',
              'Attachments',
              'Categories' => [
                'sort' => ['Categories.title' => 'ASC'],
                'queryBuilder' => function ($q) {
                  return $q->order(['Categories.title' => 'ASC'])->limit(6);
                }
              ]
            ])->first();

The limit works but I don't know how to paginate yet

Solution

The paginator doesn't support paginating associations, you'll have to read the associated records manually in a separate query, and paginate that one, something along the lines of this:

$product = $this->Products
    ->findBySlug($slug_prod)
    ->contain(['Metas', 'Attachments'])
    ->first();

$categoriesQuery = $this->Products->Categories
    ->find()
    ->innerJoinWith('Products', function (\Cake\ORM\Query $query) use ($product) {
        return $query->where([
            'Products.id' => $product->id,
        ]);
    })
    ->group('Categories.id');

$paginationOptions = [
    'limit' => 6,
    'order' => [
        'Categories.title' => 'ASC'
    ]
];

$categories = $this->paginate($categoriesQuery, $paginationOptions);

$this->set(compact('product', 'categories'));

Then in your view template you can display your $product and separately paginate $categories as usual.

See also

• Cookbook > Controllers > Components > Pagination
• Cookbook > Views > Helper> Paginator
• Cookbook > Database Access & ORM > Query Builder > Filtering by Associated Data

Answered By - ndm

Answer Checked By - David Goodson (PHPFixing Volunteer)

Read More

[FIXED] How to access Parent of belongs_to attribute

Issue

I have the following models:

class League < ApplicationRecord
    has_many :games
end

class Game < ApplicationRecord
    belongs_to :league
end

In my user's show.html.erb I'm trying to display a user's games and the league associated with the game via this snippet game.league.title and this is the view:

<div class="hidden text-center" id="tab-settings">
  <% current_user.games.each do |game| %>
    <ul>
      <li class="w-1/2 mb-4 text-left border-2 rounded-md border-coolGray-900">
        <p class=""><%= game.start_time&.strftime("%a %b %d, %Y %l:%M%P")  %> - <%= game.end_time&.strftime("%l:%M%P")  %></p>
        <p class="mb-2"><%= game.league.title %> - <%= game.home_team %> vs <%= game.away_team %></p>
      </li>
    </ul>
  <% end %>
</div>

game.league.title returns undefined method "title" for nil:NilClass error; however, when I go into the console, game.league.title querys perfectly.

Following the advice given here, I tried the following in the view:

<p class="mb-2"><%= game.league.try(:title) %> etc...</p>

and it works perfectly.

Why does game.league.try(:title) work but game.league.title return an error?

Solution

You have bad data. If you want to be able to call game.league without potential nil errors you need the games.league_id column to be defined as NOT NULL. Game.where(league_id: nil) will give you a list of the records with nulls.

Since Rails 5 belongs_to applies a presence validation to the column by default. This however doesn't prevent null values from sneaking in anyways if you use any of the methods that circumvent validations. Or if the records were created outside of Rails or even in an older version of Rails.

If you want league to be nullable you can use the safe navigation operator:

<p class="mb-2"><%= game.league&.title %> etc...</p>

Object#try is an ActiveSupport method that predates the safe navigation operator which was introduced in Ruby 2.3. While it does have its uses the operator generally should be preferred.

You can also use Module#delegate:

class Game
  # ...
  delegate :title, to: :game, allow_nil: true, prefix: true
end

<p class="mb-2"><%= game.league_title %> etc...</p>

Answered By - max

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)

Read More

[FIXED] Creating Association with condition using other association in CakePHP 3

Issue

I'm building a cake php 3 app. My app model includes 3 Tables (amongst others):

• Structures
• MeasuringPoints
• DeviceTypes

where each Strcuture can have multiple MeasuringPoints:

// StrcuturesTable.php
...
public function initialize(array $config)
{
    parent::initialize($config);

    ...

    $this->hasMany('MeasuringPoints', [
        'foreignKey' => 'structure_id'
    ]);
}

Further, each measuring point is of a certain device type:

// MeasuringPointsTable.php
...
public function initialize(array $config)
{
    parent::initialize($config);
    ...
    $this->belongsTo('DeviceTypes', [
        'foreignKey' => 'device_type_id',
        'joinType' => 'INNER'
    ]);
}

What i'm lookong for, is how to create a 'SpecialMeasuringPoints' association in the Structure table.

Somewhat like:

// MeasuringPointsTable.php
...
    $this->hasMany('SpecialMeasuringPoints',[
            'className' => 'MeasuringPoints',
            'foreignKey' => 'structure_id',
            'conditions' => ['MeasuringPoints.DeviceTypes.id'=>1]
    ]);

As you may see, I want only those measuring points, whose associated device type has the id 1. However, the previous association condition is not valid; and i have no clue how to correctly implement this.

Any help is appreciated.

Solution

Correct, that condition is invalid, for a number of reasons. First of all paths aren't supported at all, and even if they were, you already are in MeasuringPoints, respectively SpecialMeasuringPoints, so there would be no need to indicate that again.

While it would be possible to pass a condition like:

'DeviceTypes.id' => 1

That would require to alawys contain DeviceTypes when retrieving SpecialMeasuringPoints.

I would suggest to use a finder, that way you can easily include DeviceTypes and match against your required conditions. Something like:

$this->hasMany('SpecialMeasuringPoints',[
    'className' => 'MeasuringPoints',
    'foreignKey' => 'structure_id',
    'finder' => 'specialMeasuringPoints'
]);

In your MeasuringPoints class define the appropriate finder, for example using matching(), and you should be good:

public function findSpecialMeasuringPoints(\Cake\ORM\Query $query) {
    return $query
        ->matching('DeviceTypes', function (\Cake\ORM\Query $query) {
            return $query
                ->where([
                    'DeviceTypes.id' => 1
                ]);
        });
}

Similar could be done via the conditions option when passing a callback, which however is less DRY:

$this->hasMany('SpecialMeasuringPoints',[
    'className' => 'MeasuringPoints',
    'foreignKey' => 'structure_id',
    'conditions' => function (
            \Cake\Database\Expression\QueryExpression $exp,
            \Cake\ORM\Query $query
        ) {
            $query
                ->matching('DeviceTypes', function (\Cake\ORM\Query $query) {
                    return $query
                        ->where([
                            'DeviceTypes.id' => 1
                        ]);

            return $exp;
        }
]);

It should be noted that in both cases you need to be aware that such constructs are not compatible with cascading/dependent deletes, so do not try to unlink/delete via such associations!

See also

• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Custom Finder Methods
• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Filtering by Associated Data

Answered By - ndm

Read More

[FIXED] Cant save data in multiple tables using cakePHP 3.7

Issue

Been trying to save data into other table when saving Fisica entity to the Database, since I am new to cakephp it seems impossible right now, have seen multiple posts, videos, documentations and none seems to work. Please somebody help me figure out what is wrong with that.

I found a huge mistake in my relations between the entitys, now it works like that: Fisica belongsto Pessoa

associations at FisicasTable.php

$this->belongsTo('Pessoa')
        ->setForeignKey('id_pessoa')
        ->setJoinType('INNER')
        ->setClassName('Pessoas');

Fisicas Controller

$fisica = $this->Fisicas->newEntity();
    if ($this->request->is('post')) {
        $fisica = $this->Fisicas->patchEntity($fisica, $this->request->getData(),['associated' => 'Pessoa']);
        if ($this->Fisicas->save($fisica))...

Fisica add.ctp

<legend><?= __('Add Fisica') ?></legend>
    <?php
        echo $this->Form->control('nr_cpf');
        echo $this->Form->control('dt_nascimento');
    ?>
    <legend><?= __('Add Pessoa') ?></legend>
    <?php
        echo $this->Form->control('pessoa.vr_nome');
        echo $this->Form->control('pessoa.nr_telefone');
        echo $this->Form->control('pessoa.vr_email');
    ?>

I am getting the following error: error

I expected this code to insert in the two tables when submitting a new register to Fisica add.ctp.

Solution

I found that i had an issue naming my tables in the database combined with the primary key naming pattern. Simply updating the names of the tables to "Fisicas" and "Pessoas" and changing their Primary and Foreign key's to fisica_id and pessoa_id and generating all the code with bake (cake bake all pessoas, for example) resolved the problem (and generated the associations automatically).

Answered By - Natan Riboli

Read More

[FIXED] CakePHP Authentication Plugin Identity Associations

Issue

I'm using CakePHP 3.8 and migrating to the Authentication Plugin (https://book.cakephp.org/authentication/1.1/en/index.html).

When calling $this->Authentication->getIdentity()->getOriginalData() in a controller, I'd like to access a couple of assocations of my User entity.

At the moment, I'm doing this by implementing the following IdentityInterface method in my User entity:

public function getOriginalData() {
  $table = TableRegistry::getTableLocator()->get($this->getSource());
  $table->loadInto($this, ['Activities', 'Clients']);
  return $this;
}

But I feel there should be a contain parameter somewhere within the Plugin configuration (as there was with the AuthComponent).

Can anyone guide me on how to include assocations on the User entity when calling getIdentity()?

Solution

The contain option of the authentication objects for the old Auth component has been deprecated quite some time ago, and the recommended method is to use a custom finder, and that's also how it's done in the new authentication plugin.

The ORM resolver takes a finder option, and it has to be configured via the used identifier, which in your case is probably the password identifier, ie something like:

$service->loadIdentifier('Authentication.Password', [
    // ...
    'resolver' => [
        'className' => 'Authentication.Orm',
        'finder' => 'authenticatedUser' // <<< there it goes
    ],
]);

In the finder method in your table class (probably UsersTable) you can then contain whatever you need:

public function findAuthenticatedUser(\Cake\ORM\Query $query, array $options)
{
    return $query->contain(['Activities', 'Clients']);
}

See also

• Cookbook > Controllers > Components > AuthComponent > Customizing The Find Query
• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Custom Finder Methods
• Authentication Cookbook > Identifiers
• Authentication Cookbook > Identifiers > ORM Resolver

Answered By - ndm

Read More

[FIXED] How to do association between a table with an ID and another table using the same ID but twice

Issue

In cakephp, I have a table users with primary key ID and another table friends with 2 foreign keys user_id, friend_id, both indexed to the same primary key in users table. I wanna know, how the heck do I connect them in the model?

Thanks!

Solution

You can use table alias.

    $this->belongsToMany('Friends', [
        'className' => 'Users',
        'foreignKey' => 'friend_id',
        'targetForeignKey' => 'user_id',
        'joinTable' => 'users_friends',
    ]);

Answered By - Kani

Read More

[FIXED] How to use CakePHP ORM to query belongsToMany relationship

Issue

I am working on a custom query builder piece of code that supports a CakePHP 3 based API. I recently got a request to make it search against a Task item for associated Users.

The association from a Task to users is a belongsToMany, which has not been supported in the custom query functionality up to this point. The relationship looks like this in the TasksTable.php:

<?php

$this->belongsToMany('Users', [
    'foreignKey' => 'task_id',
    'targetForeignKey' => 'user_id',
    'through' => 'TasksUsers'
]);

The query builder uses Cake\Database\Expression\QueryExpression to execute queries, since it has to handle more complex queries than I am able to handle with Cake\Database\Query. However I am now trying to nest a QueryExpression to handle querying Users on this belongsToMany relationship.

The code is pretty abstracted. For the sake of simplicity, I simplified my implementation of Search\Model\Filter\Base on the TasksTable to the code below. This demonstrates the issue I am dealing with precisely when I run it.

<?php

public function process(): bool
{
    $this->getQuery()->where(function (QueryExpression $exp, Query $query) {
        return $query->newExpr()->eq('Users.id', 621048);
    })->contains('Users');
}

The above gives me:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Users' in 'where clause'"

I am wondering how do I modify the return on the nested function in the where clause and the contents of the contains to accomplish a join on the belongToMany relationship against Users?

On a side note, I am aware that the example query can be performed using the matching() method against the Query, as demonstrated here. However this does not work for my solution, due to the fact that everything in the custom search has to be checked using QueryExpressions to be compatible with the rest of the query builder.

Thanks in advance for your input!

Solution

As helpfully noted in the comments, the solution to my question was to add a leftJoinsWith() to the query, as opposed to the contains():

<?php

public function process(): bool
{
    $this->getQuery()->where(function (QueryExpression $exp, Query $query) {
        return $query->newExpr()->eq('Users.id', 621048);
    })->leftJoinWith('Users');
}

QueryExpressions are for checking conditions and not joining data.

More on using leftJoinWith() can be found here.

Answered By - space97

Read More

[FIXED] CakePHP: Unable to save data to parent entity via belongsTo associated child

Issue

Problem description

I'm trying to configure a CakePHP 3.7 API to save associated data in a child-first manner. The entities - for the sake of example, lets call them Users and Persons - and their relationships are as follows:

UsersTable.php

...
  $this->belongsTo('Persons', [
    'foreignKey' => 'person_id',
    'joinType' => 'LEFT',
    'className' => 'MyPlugin.Persons',
  ]);
...

PersonsTable.php

  $this->hasOne('Users', [
    'foreignKey' => 'person_id',
    'className' => 'MyPlugin.Users'
  ]);

In their respective entities, they each have one another's property visibility set to true. What I'm trying to do is POST to the /users/ route (UsersController.php) and have it also save the Persons object included. The payload is as such:

{
    "username": "foo",
    "password": "bar",
    "persons": {
        "dob": "1982-07-03",
    }
}

The relevant part of the saving method is below, from UsersController.php:

  if ($this->request->is('post') && !empty($this->request->getData())) {
    $data = $this->request->getData();
    $newEntity = $this->Users->newEntity($data, ['associated' => 'Persons']);

    $savedEntity =  $this->Users->save($newEntity);
  ...

The error

This produces the following SQL error.

PDOException: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column 'person_id' violates not-null constraint DETAIL: Failing row contains (1, null, foo, bar)

I understand this is because Cake is attempting to save to Users without having a person_id to satisfy the foreign key constraint. It's not possible to reverse this FK relationship in my application domain as we desire leftward one-to-many relationship (User -> 1 Person).

I suspect sending an id in the persons object of the JSON payload will allow this to save correctly. However, for various reasons, this isn't possible at runtime. For example, this is how it's shown in the "Saving Data" CakePHP Book page...

$data = [
  'title' => 'First Post',
  'user' => [
      'id' => 1,
      'username' => 'mark'
  ]
];

...
$article = $articles->newEntity($data, [
    'associated' => ['Users']
]);

$articles->save($article);

I know the following would also likely work as suggested by xPfqHZ for a similar issue, as Persons can save to Users, but it feels less suitable as compared to what I'm trying to do and feels as if there is a way via the associations on Users.

  if ($this->request->is('post') && !empty($this->request->getData())) {
    $data = $this->request->getData();
    $newEntity = $this->Users->Persons->newEntity($data, ['associated' => 'Persons']);

    $savedEntity =  $this->Users->Persons->save($newEntity);
  ...

Workings

Now I believe this used to be possible in CakePHP 2.X, as stated in this answer by ndm on a similar question where a person is attempting to save the belongsTo associated entity and it's parent hasOne entity in one request via the belongsTo entity.

That's the expected behavior, saveAssociated() is not meant to save only the associated records, it will save the main record as well, so you should use saveAssociated() only, no need to manually set the foreign key, etc, CakePHP will do that automatically.

Controller

public function create() {
    if ($this->request->is('post') && !empty($this->request->data)):
        $this->CandidatesProblemReport->create();
        if ($this->CandidatesProblemReport->saveAssociated($this->request->data)):
            // ...
        endif;
    endif;
}

However, I'm not able to find or use the saveAssociated() method upon the Cake\ORM\Table object which the Users entity inherits from, in the documentation. Calling it produces a method not found error. This method only appears to exist on the Cake\ORM\Association object as detailed in the documentation. Unless I'm missing the obvious, is there a way to use this or is it used internally by BelongsTo() and its sibling methods?

Logging / Dumping entity

Using Cake\Log\Log::error($newEntity); or die(var_dump($newEntity)); shows the Users data of the payload hydrated into an object, but I don't see the Persons object attached (see below).

object(MyPlugin\Model\Entity\User)[299]
  public 'username' => string 'foo' (length=3)
  public 'password' => string 'bar' (length=3)
  public '[new]' => boolean true
  public '[accessible]' => 
    array (size=5)
      '*' => boolean false
      'person_id' => boolean true
      'username' => boolean true
      'password' => boolean true
      'person' => boolean true
  public '[dirty]' => 
    array (size=2)
      'username' => boolean true
      'password' => boolean true
  public '[original]' => 
    array (size=0)
      empty
  public '[virtual]' => 
    array (size=0)
      empty
  public '[hasErrors]' => boolean false
  public '[errors]' => 
    array (size=0)
      empty
  public '[invalid]' => 
    array (size=0)
      empty
  public '[repository]' => string 'MyPlugin.Users' (length=17) 

Attempting to \Cake\Log\Log::error($savedEntity); shows nothing in the log file.

save() associations arguments

Another solution I considered was using the $options['associated] of save() as shown in the documentation (extract below). With this set to true as below, the error still occurred.

save( Cake\Datasource\EntityInterface $entity , array $options [] )

... associated: If true it will save 1st level associated entities as they are found in the passed $entity whenever the property defined for the association is marked as dirty. If an array, it will be interpreted as the list of associations to be saved. It is possible to provide different options for saving on associated table objects using this key by making the custom options the array value. If false no associated records will be saved. (default: true) ...

UsersController.php:

  if ($this->request->is('post') && !empty($this->request->getData())) {
    $data = $this->request->getData();
    $newEntity = $this->Users->newEntity($data, ['associated' => 'Persons']);

    $savedEntity =  $this->Users->save($newEntity, ['associated' => true]);
  ...

Summary

Without going through the PersonsController.php and utilising its hasOne relationship, I'm not having much luck getting my Users and Persons data to save through the UsersController.php.

If I've missed any important information, or you have questions/need more, please ask! I might have missed something obvious, but I'd appreciate any suggestions/solutions possible.

Solution

As @ndm identified, the error lay in the posted data. As per the "Saving Data: Saving BelongsTo Associations" page of the documentation:

When saving belongsTo associations, the ORM expects a single nested entity named with the singular, underscored version of the association name.

The posted key persons should have been person. Equally, if the entity were named PersonSnapshots, the relevant key in the payload hydrated into the entities would need to have been person_snapshot.

Answered By - cognophile

Read More

[FIXED] CakePHP 3 - Users belongsToMany Users

Issue

I have a specific request, to build an association between users. This causes me confusion, how to reduce duplicate associations, query and results?

The starting point would look like this?

// UsersTable
$this->belongsToMany('Users', [
            'through' => 'Connections',
        ]);

How to fetch all associations in one query, regardless of whether users key in "user_from" or "user_to" field?

Solution

How about using aliases?

Your users table:

class UsersTable extends Table 
{

    public function initialize(array $config)
    {
        $this->hasMany('ToConnections', [
            'className' => 'Connections',
            'foreignKey' => 'user_to'
        ]);

        $this->hasMany('FromConnections', [
            'className' => 'Connections',
            'foreignKey' => 'user_from'
        ]);

    }
}

And your connections table:

class ConnectionsTable extends Table 
{

    public function initialize(array $config)
    {
        $this->belongsTo('ToUsers', [
            'className' => 'Users',
            'foreignKey' => 'user_to'
        ]);

        $this->belongsTo('FromUsers', [
            'className' => 'Users',
            'foreignKey' => 'user_from'
        ]);

    }
}

You can then use contain() to load associated models as required.

$query = $conections->find()->contain([
    'ToUsers',
    'FromUsers'
]);

$recipients = TableRegistry::get('users');
$query = $recipients->find()->contain([
    'ToConnections.FromUsers',
]);

Answered By - Inigo Flores

Read More

[FIXED] Simple Count and Sort on Association Cakephp 3

Issue

I have students table and results table. Student has many results. A result is associated with only one student. So please what I'm trying to achieve is something like this:

$this->Students
    ->find('all')
    ->contain('Results')
    ->order('by count of results each student has' => 'asc');

Any help would be greatly appreciated.

Solution

Try this

$query = $this->Students->find()
    $query->select(['total_result'=> $query->func()->count('Results.id')])
    ->autoFields(true)
    ->contain('Results')
    ->leftJoinWith('Results')
    ->group(['Students.id'])
    ->order(['total_result'=>'ASC']);

debug($query->all());

More check Here

Answered By - tarikul05

Read More

[FIXED] Join Table with the id of another join table

Issue

i'm currently trying to build a backend for a project. In this project you will be able to create "ContentElements" that can be used to display content in a page (in my case Sites). Every ContentElement can have multiple options. When a user creates a new Site with an ContentElement (e.g. header) he would enter all options of the element. For example:

"src": "/img/bg.jpg",
"text": "Lorem ipsum..."

In order to save the option's value per page it is used in, i store these values in a separate table (content_elements_sites_values).

My scheme currently looks like this: data scheme

So what i'm currently trying to do is when i get all data associated with the Site i also want to get the data from 'content_elements_sites_values'

$site = $this->Sites->get($id, [
        'contain' => ['Templates', 'Colors', 'SiteTypes', 'ContentElements' => [
            'ContentElementOptions' => [
                'ContentElementsSitesValues'
                ]
            ]
        ],
        'conditions' => [
            // Just to explain my problem.
            'ContentElementsSites.id' => 'ContentElementsSitesValues.content_elements_sites_id'
        ]
    ]);

I really don't know if this is even possible or even if my "design" is a total bull***t. But i cannot think of another way to store the filled in data. I'm very open to suggestions of a better way to store this data. Please ask if you need further information in order to help me.

Thank you in advance!

---

EDIT

I try to explain better what i want to achieve.

Every site can have multiple content_elements of the same type (association is stored in content_elements_sites junction table).
Every content_element can have multiple content_element_options

All content_element_options and content_elements are defined by an Admin.

Another user can create a site and populate it with content_elements and enter content_elements_sites_value for all content_element_options. And as the same content_element (e.g. a paragraph or a list) can have multiple occurrences in the same site, i'll need to store every content_elements_sites_value the user entered.

Thats why i created the link between content_elements_sites and content_element_options.

Currently i'm using this query to get everything expect the value:

$site = $this->Sites->find('all', [
        'conditions' => [
            'Sites.id' => $id
        ],
        'contain' => ['ContentElements' => [
                'sort' => [
                    'ContentElementsSites.order' => 'ASC'
                ],
                'ContentElementOptions' => [
                    'ContentElementsSitesValues' => [
                        'conditions' => [
                            'ContentElementsSitesValues.content_elements_sites_id' => 'ContentElementsSites.id',
                        ]
                    ]
                ]
            ]
        ]
    ]);

This results in empty content_elements_sites_values

(int) 1 => object(App\Model\Entity\ContentElementOption) {
    'id' => (int) 7,
    'content_element_id' => (int) 1,
    'name' => 'Test',
    'json_name' => 'test',
    'option_type_id' => (int) 1,
    'content_elements_sites_value' => null,             
}

My scheme currently looks like this: data scheme

I'm wondering if this query is even possible. Or if the whole thing is just too flexible.

Solution

The way you have defined the relationships signifies that you wish to have a very modular approach so that a content element can be used with multiple sites and a content element option can be used with multiple content elements.

If that is the case, schema direction looks okay with few changes :

1) content_elements_sites_values table can have site_id column directly instead of content_elements_sites_id column as site will be always unique for an entry in that table so the connection id of content_elements_sites isn't required.

2) content_elements_sites_values table can be renamed to content_element_options_values.

3) You can remove id column from content_elements_sites and content_elements_sites_values junction tables.

Answered By - Gaurav

Read More

[FIXED] CakePHP 3 : show associated data along with find('all')

Issue

I'm working on CakePHP 3.2. I have two tables categories and subcategories where subcategories is associated with categories with foreign key category_id.

I have to build a drop down navigation using these two tables. So that It will look like this

-Menu
|- Category_1
   |- Category_1_subcategory_1
   |- Category_1_subcategory_2
   |- Category_1_subcategory_3
|- Category_2
   |- Category_2_subcategory_1
   |- Category_2_subcategory_2
|- etc

For this this is what I have done. In AppController.php

// set navigation menu
$this->loadModel('Categories');
$menu_categories = $this->Categories->find('all', [
  'contain' => ['Subcategories'],
]);
$this->set('menu_categories', $menu_categories);

Then in navigation.ctp

$foreach($menu_categories as $menu_category):
   echo $menu_category->title;
   foreach($menu_category->Subcategories as $subcategory):
      echo $subcategory->title;
   endforeach;
endforeach;

But this prints only category->title and not subcategories

I have to print subcategories under each belonging category.

Solution

Make sure to define the associations in the models.

Category Model:

$this->hasMany(
   'Subcategory', [
        'className' => 'Subcategory',
        'foreignKey' => 'category_id'
]);

Subcategory Model:

$this->belongsTo(
   'Category', [
        'className' => 'Category',
        'foreignKey' => 'category_id'
]);

http://book.cakephp.org/3.0/en/orm/associations.html

Answered By - bill

Read More

[FIXED] Left Join CakePHP3

Issue

I'm trying to do a LEFT JOIN in CakePHP3. But all I get is a "is not associated"-Error.

I've two tables BORROWERS and IDENTITIES. In SQL this is what I want:

SELECT
 identities.id
FROM
 identities
LEFT JOIN borrowers ON borrowers.id = identities.id
WHERE
 borrowers.id IS NULL;

I guess this is what I need:

$var = $identities->find()->select(['identities.id'])->leftJoinWith('Borrowers',
        function ($q) {
               return $q->where(['borrowers.id' => 'identities.id']);
        });

But I'm getting "Identities is not associated with Borrowers".

I also added this to my Identities Table:

$this->belongsTo('Borrowers', [
  'foreignKey' => 'id'
]);

What else do I need? Thanx!

Solution

Yup. It was an instance of \Cake\ORM\Table, due to my not well chosen table name (Identity/Identities). I guess it's always better not to choose those obstacles, for now I renamed it to Label/Labels.

This query now works perfectly:

$var = $identities 
  ->find('list')
  ->select(['labels.id'])
  ->from('labels')
  ->leftJoinWith('Borrowers')
  ->where(function ($q) {
      return $q->isNull('borrowers.id'); 
});

Answered By - DeVolt

Read More