Issue Saving Data From Related Table

[mcnally486]

I have three tables 

 

tbl_customer

customer_ID(PK)

...

....

 

tbl_job

job_ID

customer_ID(FK)

...

...

 

tbl_jobNumber

ID(PK)

jobNumber

customer_ID (FK)

 

 

I am trying to find a solution where if i save a new job, 

1. the app will look in tbl_jobNumber for a jobNumber corresponding to the customer_ID of the tbl_job

2. save that as tbl_job.job_ID

3. increment the value in tbl_jobnumber.jobNumber by one (ready for use on the next job)

 

i have tried a few thing including query builder, placing code in beforeSave in the job model ....

 

i have job model with the following relations

 

'customer' => array(self::BELONGS_TO, 'Customers', 'customer_ID'),

 

and in the customer model the relationships are 

 

'jobs' => array(self::HAS_MANY, 'Jobs', 'customer_ID'),  

'jobNos' => array(self::HAS_ONE, 'JobNo', 'Customer_ID'),

 

i would have thought that i could just do 

$model->job_ID = $model->customer->jobNos->JobNo

But that isn't working , can anyone offer any solutions to my problem ?

[Edward]

Just validate and save the models that you have aquired your data in. The model relations have nothing to do with the saving of the data and are only required when you need to fetch data which you would usually do with your regular inner or outer table joins.

 

http://www.yiiframework.com/doc/guide/1.1/en/database.ar

 

Creating Record

To insert a new row into a database table, we create a new instance of the corresponding AR class, set its properties associated with the table columns, and call the save() method to finish the insertion.

$post=new Post;$post->title='sample post';$post->content='content for the sample post';$post->create_time=time();$post->save();

[mcnally486]

Hi and thanks for the response, i understand what you are saying but a value that in i need in the record before save  exists in another table related to customers and not jobs  . if can work out how to get this value into the record i will be halfway there the value exists in customer(relatedtojobs)->jobno(related to customer) ->jobnumber . Thanks for your help

[Edward]

Well its simple you just need to get the Primary key, the ID value from the last table saved you can do this by

 

say your model was called

 

$modelEmployes

 

Once you got your data saved with

$modelEmployes->save();

you can get the id of that last model by referencing like this

$modelEmployes->id

So

 

$modelBusiness->employ_id = $modelEmployes->id;

[mcnally486]

Sorry i don't think i am explaining this properly or am not understand what you are saying but i appreciate your time 

i am saving a job record  from a form, once this submitted i need to assign a job number to this job record, the job number is not an id it is held in another table 

the tbl_jobnumber does nothing but hold a number that is used for this purpose , once it is used (assigned to a job record ) it needs to be incremented by one (to be used next time for that customer )

 

$modelJob->jobnumber = number from tbl_jobnumber that has the same customer_id as the job that has just been created 

 

Thanks for helping

[Edward]

Yeah but looking at your tables i think you only need 2 anyway, why don't use use the job id from the 2nd table as your job number, that makes more sense to me.

[mcnally486]

This is where the headache started , i am remaking some software from an existing app/database and one of the requirements is that certain customers wanted job numbers starting from a certain range i.e 500 or if they started at 1 they wouldn't want their next job to be 25 if more jobs had been added to other customers, 

Its sort of tailoring the job number to the customer , i agree it would be easier to use the job id from the second table as that auto-increments.

I am open to ideas of workarounds but i am new to yii and almost all functionality is done except for this and i feel like i have hit a brick wall !

 

I was thinking about doing a custom SQL query to sort it but am unsure how to do it in yii 

$model->jobnumber = SELECT JobNo FROM tbl_JobNo where Customer_ID = $model->customer_ID

[Edward]

Well instead of making a third table, just add another column to the 2nd table, then add the job number in there separately. You could use whichever algorithm you wished to get the job number unique, higher or whatever for the other customer.

 

Good luck with your app, yii with is controller and models and especially with its CRUD functionality makes it easy to implement those features but it does take time to get to grips with it.

[Edward]

I see what you are trying to do, only one customer can have one job and you only wish to have one row on the jobs table for that, then when the customer finishes the job he will have the job number renewed. To be quite honest though i think this is ugly, i think it would just be better to fill the table with new jobs every time, that way you only need two tables. If a customer had one job not complete he would not be able to add another job until that one was completed, that's simple and you also have a history of all the jobs completed for some analysis. If i were you i would change the structure of your design a little as it makes it easier to code and the app will have more useful functionality.

[mcnally486]

Your nearly there with what i mean, by customer i mean company , and they have many jobs , however they would prefer their job numbers to increment by one each time , for their records it would be job 501, 502, 503, 504 etc. The schema is already there with many jobs contained in the database .

The point of the third table is say the company requests a  new job , the app looks into the tbl_jobNumber (and the row that has their customer_id)  to retrieve unique number for them say 505 which is then  inserted into the job_num field of the tbl_jobs table (which contains the details of the job) .

the number in the jobnumber table for that company would then be incremented to 506 (which is what would be used for the next job by that customer . see what i mean ?

This is how it was done in the VB,net app that existed before this one and i think it is still the best solution 

[Edward]

Okay i see what you mean now so that company can have many jobs and each with baseline of 500 incrementing by one each time. That table solution seems fine. But Ah just wait, why not just put an extra column in the first table for company with job number, then you don't need a third table.

 

bl_customer

customer_ID(PK)

jobNumber

...

....

 

tbl_job

job_ID

customer_ID(FK)

...

....

[Edward]

So if you wanted to get the jobNumber from customer and increment by one you would do this.

 

$modelCustomer = Customer::model()->findByPk($customerID);

 

$modelCustomer->jobNumber++;

 

$modelCustomer->saveAttributes(array('jobNumber'));

[Edward]

Otherwise if you do this with three tables you will need to do the following:

 

$modelCustomer = Customer::model()->with('jobNos')->findByPk($customerID);

 

$modelCustomer->jobNos->jobNumber++;

 

$modelCustomer->saveAttributes(array('jobNumber'));

[mcnally486]

you the man i think your onto something there !!!

[mcnally486]

yup it would appear that adding a Jobnumber field to the customer table will save me a lot of stress, thanks man !!

[mcnally486]

So if you wanted to get the jobNumber from customer and increment by one you would do this.

 

$modelCustomer = Customer::model()->findByPk($customerID);

 

$modelCustomer->jobNumber++;

 

$modelCustomer->saveAttributes(array('jobNumber'));

 

One more question , i am trying to implement this code in the job controller and i cant get it working , any ideas ?

[Edward]

If you haven't purchased the Yii Book already i would advise you to do so and spend sometime reading it. I like to help but i can't be there to hold your hand through your whole project, i have my own work you know. Yii can be quite a challenge to start of with but after reading a few chapters of Larry's book you will be able to hit that wall you were speaking of like a BombShell.