Custom Query - Schedule / User ID Information

Discussion for TimeTrex open source community developers.
Locked
visaolive
Posts: 21
Joined: Mon May 20, 2013 5:16 am

Custom Query - Schedule / User ID Information

Post by visaolive »

When using the getAll function within a plugin for ScheduleListFactory, simply want the following elements:

- schedule_id
- user_id
- status_id
- start_time (timestamp)
- end_time (timestamp)
- branch_id
- department_id

How might I reference these from getAll?

Or will a custom query like the one below work?

Code: Select all


$id = $this->getId();
		
		
		$limit = NULL;
		$where = NULL;
		$order = NULL;
		$page = NULL;
		
		$query = "
					select 	
						s.id as schedule_id,
						s.user_date_id as user_date_id,
						u.user_id as user_id,
						s.status_id as status_id,
						s.start_time as start_time,
						s.end_time as end_time,
						s.branch_id as branch_id,
						s.department_id as department_id
					from
						schedule s,
						user_date u
					WHERE 
						s.deleted = 0 AND
						u.deleted = 0 AND
						s.user_date_id = u.id AND
						s.id = '$id'";
		$query .= $this->getWhereSQL( $where );
		$query .= $this->getSortSQL( $order );

		$result = $this->ExecuteSQL( $query, NULL, $limit, $page );

shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Custom Query - Schedule / User ID Information

Post by shaunw »

Even though the plugin system is quite powerful, it should only be used when absolutely necessary. Creating your own SQL queries will guarantee that they will break at some point down the road when table schemas change (and they do quite often). Therefore to avoid this as much as possible we recommend against doing anything directly with SQL, instead you should use the API functions such as, APISchedule->getSchedule().

You can pass all kinds of things into the API functions, such as custom filters, a list of columns you want returned, etc... See api/soap/api_client_example.php for example usage. The API can be used "locally" as well as "remotely".
Locked