Thread

I have a mysql question for everyone.  In your pivot tables or you put each column in its own index or put all 3 of the columns in one index?  I am not really sure what is best.

Thanks

Hi. I’m not experienced in DBs so what you’d advice in this situation:
We have table complaints and sometimes (but not every time) complaint has optional feature named otherType.
I decided to store them in a table otherTypes that looks like
complaint_id(INT) | otherType(TEXT)

so i don’t loose disk space with unset fields directly in complaints table.

Question is: can this be done with support of DM?

How i handle things like that with datamapper is build a table of types and then have a pivot table point the complaint to the type.

complaints -> complaints_othertypes -> othertypes

This way you could have complaints with multiple other types.

then you have an easy way to access this

$complaint->othertype->get() //will get all your other types for the complaint

you can alls do a reverse lookup

$othertype->complaint->get()  //get all the complaints of a specific type

Well thx, but i understood this part from reading the manual 8)
The usual approach using complaints_otherTypes table doesn’t make sence here, that’s why i wanted a piece of advice. Let me explain more deeply:

complaints has field “type” and there are some particular values of type, when user of DB enters something and it goes to the otherTypes. So i want to avoid giving complaints table additional field to describe typeOther because probably there will be no typeOther for the most of entries.
It means when type takes one of magic values, i look for type in otherTypes not in types…
Of course this mechanism is slightly clumsy.
And that’s why i need an advice 8)

@Oblique
First, how much space are you really going to waste having one extra null field?  Have you done any research?  I think you you are over-complicating something unnecessarily.

Also, it appears that the type field is a number, correct?  Instead of storing the type number directly in the complaints table, why don’t you relate Complaints to a generic ComplaintTypes table.  Then, pre-fill in the default values on the table.

Now, when someone chooses a default type, you simply relate that item.  If they add a custom type, you save it to ComplaintTypes, and relate that one.  Now you don’t have to do anything special to deal with “other” types vs “normal” types.

One benefit of this method is, if there isn’t a security risk, you could actually use a little JavaScript to suggest values from the Types table as someone is typing them in.  This may or may not work depending on your users, what gets typed in, and if you want to maintain that.

If you are using my extended version of DataMapper, you don’t even have to add an extra table to join the two, you can just add a column complainttype_id to your complaints table.

>I think you you are over-complicating something unnecessarily.
Consider this as my favourite activity. )

Thank you for reasonable answer, i think it’ll help.

Not sure how self relations work in the new version.

Heres my table:

Members
- id
- sid
- name
- created_on
- parent_member_id

I want to be able to relate parent_member_id to id so I can set the parent member account.

class Member extends DataMapper
{
    var $table = 'members';

    var $has_many = array(  "problems", );
// Need to relate to itself. (parent_member_id to id)

    var $validation = array(
        array(
            'field' => 'sid',
            'label' => 'Member SID',
            'rules' => array('trim', 'min_length' => 2, 'max_length' => 255)
        ),
        array(
            'field' => 'name',
            'label' => 'Members Full Name',
            'rules' => array('trim')
        )
    );

    /**
     * Constructor
     *
     * Initialize DataMapper.
     */
    function Member()
    {
        parent::DataMapper();
    }

    // --------------------------------------------------------------------

}

$has_one = array(
    // Child's side of the self relationship
    'parent_member' => array(
        // parent_member is really just a 'member'
        'class' => 'member'
    );
);

$has_many = array(
    // Parent's side of the self relationship
    'member' => array(
        // The parent is joined as 'parent_member'
        'other_field' => 'parent_member'
    );
);

To access the parent:

$member = new Member();
$member->get_by_id($this->input->post('id'));
$parent = $member->parent_member->get();

To access the children:

$children = $member->member->get();
foreach($children->all as $child) {
    ...
}

Saving:

$member->save_parent_member($parent);
// OR:
$member->save(array(
    'parent_member' => $parent
));

// Saving children onto the parent
$parent->save_member($children->all);

// etc.

Thanks

$u = new User();
  $u->get_where( array(‘email’ => ‘[email protected]’) );
 
  $u->package->get(); <—Something wrong here with this method
 
  echo $u->package->title;

I keep getting this error

A Database Error Occurred

Error Number: 1054

Unknown column ‘packages.*’ in ‘field list’

SELECT `packages`.`*` FROM (`packages`) LEFT JOIN `packages_users` ON `packages`.`id` = `packages_users`.`package_id` LEFT JOIN `users` ON `users`.`id` = `packages_users`.`user_id` WHERE `users`.`id` = 1

Good Job…

Well done… 😉

@aman_tuladhar

If you read the (original) DataMapper manual, you will see that it explains that problem in detail on the troubleshooting page, using the exact error you show.

ahh… I see
Thanks a Lot

I’m having difficulty understanding how we do self referencing. In the examples in the docs, it’s assumed there are different categories to work with for models that are self referenced. What about situations where there is no way to distinguish between one record and another?

If anyone has examples for doing this, that would be great. Otherwise, I’ll just write a specific part of my application without DataMapper, which might be a problem…

Thanks.

@camporter1

You should look at my extended version of DM.  It doesn’t change the way existing DM code is written, but it provides a lot of new features, including much more powerful self-references.  I don’t have as nice of documentation, but I did try to include some examples.

You can view and download the latest version from here.  It is a drop-in replacement for DM.

Please give it a try, and you can leave feedback for me here, or through a private message.