Level 104
How is the comments relationship defined? And what does the Comment model look like?
Sep 27, 2022
2
Level 1
Laravel SQL query error
Hello. I have error in my Laravel sql query. How can I fix this?
$collection
->groupBy('panichd_tickets.id')
->select($a_select)
->with('creator')
->with('agent')
->with('owner.department.ancestor')
->withCount('allAttachments')
->withCount(['comments' => function ($query) use ($currentLevel) {
$query->countable()->forLevel($currentLevel)->where('type', '!=', 'note');
}])
->withCount(['recentComments' => function ($query) use ($currentLevel) {
$query->countable()->forLevel($currentLevel)->where('type', '!=', 'note');
}])
->withCount('internalNotes');
SQL Query:
SELECT
count(*) as aggregate
from
(
select
"panichd_tickets"."id",
"panichd_tickets"."created_at",
"panichd_tickets"."subject" as "subject",
"panichd_tickets"."hidden" as "hidden",
"panichd_tickets"."content" as "content",
"panichd_tickets"."intervention" as "intervention",
"panichd_tickets"."status_id" as "status_id",
"panichd_statuses"."name" as "status",
"panichd_statuses"."color" as "color_status",
"panichd_priorities"."color" as "color_priority",
"panichd_categories"."color" as "color_category",
"panichd_tickets"."start_date" as "start_date",
0 - date_format(
panichd_tickets.start_date, '%Y%m%d%h%i%s'
):: INTEGER as inverse_start_date,
CASE panichd_tickets.limit_date WHEN NULL THEN 0 ELSE 1 END as has_limit,
"panichd_tickets"."limit_date" as "limit_date",
0 - date_format(
panichd_tickets.limit_date, '%Y%m%d%h%i%s'
):: INTEGER as inverse_limit_date,
"panichd_tickets"."limit_date" as "calendar",
"panichd_tickets"."updated_at" as "updated_at",
"panichd_tickets"."completed_at" as "completed_at",
"panichd_tickets"."agent_id",
"panichd_tickets"."read_by_agent",
"agent"."name" as "agent_name",
"panichd_priorities"."name" as "priority",
"panichd_priorities"."magnitude" as "priority_magnitude",
"members"."name" as "owner_name",
"creator"."name" as "creator_name",
"panichd_tickets"."user_id",
"panichd_tickets"."creator_id",
"panichd_categories"."id" as "category_id",
"panichd_categories"."name" as "category",
group_concat(panichd_tags.id) AS tags_id,
group_concat(panichd_tags.name) AS tags,
group_concat(panichd_tags.bg_color) AS tags_bg_color,
group_concat(panichd_tags.text_color) AS tags_text_color,
" as dep_ancestor_name, (select count(*) from " panichd_comments " where " panichd_tickets "." id " = " panichd_comments "." ticket_id " and " type " in (reply, note, completetx) and " type " != note) as " comments_count ", (select count(*) from " panichd_comments " where " panichd_tickets "." id " = " panichd_comments "." ticket_id " and " type " in (reply, note, completetx) and " type " != note and " panichd_comments "." updated_at " > 2022-09-26 00:00:00) as " recent_comments_count " from " panichd_tickets " left join " users " on " users "." id " = " panichd_tickets "." user_id " left join " users " as " members " on " members "." id " = " panichd_tickets "." user_id " left join " users " as " creator " on " creator "." id " = " panichd_tickets "." creator_id " inner join " panichd_statuses " on " panichd_statuses "." id " = " panichd_tickets "." status_id " left join " users " as " agent " on " agent "." id " = " panichd_tickets "." agent_id " inner join " panichd_priorities " on " panichd_priorities "." id " = " panichd_tickets "." priority_id " inner join " panichd_categories " on " panichd_categories "." id " = " panichd_tickets "." category_id " left join " panichd_taggables " on " panichd_tickets "." id " = " panichd_taggables "." taggable_id " and " panichd_taggables "." taggable_type " = PanicHD\PanicHD\Models\Ticket left join " panichd_tags " on " panichd_taggables "." tag_id " = " panichd_tags "." id " where " completed_at " is null and " status_id " = 1 group by " panichd_tickets "." id ") count_row_table)
ERROR: syntax error at or near "" where ""
LINE 44: ...or_name, (select count(*) from " panichd_comments " where " ...
^
SQL state: 42601
Character: 1973
I couldn't understand where there is an error in the query, it seems right to me. If you know my mistake, can you share it with me?
Level 1
@tykus of course. comments relationship defined in Ticket Model:
/**
* Get Ticket comments.
*
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
public function comments()
{
return $this->hasMany('PanicHD\PanicHD\Models\Comment', 'ticket_id');
}
Comment Model:
class Comment extends Model
{
use ContentEllipse;
protected $table = 'panichd_comments';
/**
* All of the relationships to be touched.
*
* @var array
*/
protected $touches = ['ticket'];
public function delete()
{
$a_errors = [];
// Delete attachments
foreach ($this->attachments()->get() as $att) {
$error = $att->delete();
if ($error) {
$a_errors[] = $error;
}
}
// Delete notifications
\DB::table('panichd_comment_email')->where('comment_id', $this->id)->delete();
$error = $a_errors ? implode('. ', $a_errors) : null;
if ($error != '') {
return $error;
}
parent::delete();
}
/**
* Get emails | members whom notifications have been sent.
*
* Return @collection
*/
public function notifications()
{
return $this->hasMany('PanicHD\PanicHD\Models\CommentNotification')->orderBy('name');
}
/**
* Get related ticket.
*
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function ticket()
{
return $this->belongsTo('PanicHD\PanicHD\Models\Ticket', 'ticket_id');
}
// TODO: Delete user() method
/**
* Get comment related \PanicHDMember.
*
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function user()
{
return $this->belongsTo('\PanicHDMember', 'user_id');
}
/**
* Get Comment owner as PanicHDMember model.
*
* @return \Illuminate\Database\Eloquent\Relations\BelongsTo
*/
public function owner()
{
return $this->belongsTo('\PanicHDMember', 'user_id');
}
public function attachments()
{
return $this->hasMany(Attachment::class, 'comment_id')->orderByRaw('CASE when mimetype LIKE "image/%" then 1 else 2 end');
}
/**
* Filter visible comments depending on member level.
*/
public function scopeForLevel($query, $level)
{
// User level
if ($level < 2) {
return $query->where(function ($q1) {
// Common public comments
return $q1->whereIN('type', ['reply', 'complete', 'completetx', 'reopen'])
->orWhere(function ($q2) {
$q2->where('type', 'note')->whereHas('notifications', function ($q3) {
// Notes from where current user has been notified
return $q3->where('member_id', auth()->user()->id);
});
});
});
}
// For agent or admin
return $query;
}
/**
* Filter comment entries that are countable as real comments (complete and reopen comments are excluded).
*/
public function scopeCountable($query)
{
return $query->whereIN('type', ['reply', 'note', 'completetx']);
}
}
Please or to participate in this conversation.