Error sending messages with postgres about tbmsg HOT 37 CLOSED

Hi @raoul
It sounds Weird that it works with mysql and not with postgres.
I'll paste some code for you to replace and if it fixes the problem I'll do a commit.

from tbmsg.

@RaoulDijksman
please go to the file: "EloquentTBMsgRepository.php"

and replace the function: "isUserInConversation" with

 public function isUserInConversation($conv_id, $user_id) {
    $res = $this->db
        ->table($this->tablePrefix.'conv_users')
        ->select('conv_id', 'user_id')
        ->where('user_id', $user_id)
        ->where('conv_id', $conv_id)
        ->first();

    if(is_null($res))
        return false;
    return true;
}

Now this works with laravel query builder and not pure mysql, so the laravel query builder supports several sql programs so I hope it will help.

Waiting for your info

from tbmsg.

Thanks for the fast responce,
I will test your code right away tonight when i get home!

In the mean time this is what i found on the Postgres server:
These are the queries that are run when calling TBMsg::sendMessageBetweenTwoUsers

SELECT cu.conv_id FROM conv_users cu WHERE cu.user_id='1' OR cu.user_id='506' GROUP BY cu.conv_id HAVING COUNT(cu.conv_id)=2;

insert into "conversations" ("updated_at", "created_at") values ('2015-01-16 09:21:32', '2015-01-16 09:21:32') returning "id";

SELECT COUNT(cu.conv_id) FROM conv_users cu WHERE cu.user_id='1' AND cu.conv_id='6' GROUP BY cu.user_id, cu.conv_id HAVING COUNT(cu.conv_id)>0;

As you can see, TBMsg checks to see if the conversation exists in the table conv_users but it´s empty, so it creates a new conversation. These conversation are being made with succes! BUT the table conv_users is never being updated... The users are never added to the conversation.

Raoul

from tbmsg.

ohhh ok, so in that case, the code I send is not related.

You say that it doesnt add the users to conv_user table.

Let me check as this is a core functionality, without it many things should break

from tbmsg.

After looking at your code I can see there a Try - Catch block in Tzookb/TBMsg/Repositories/EloquentTBMsgRepository.php under the function createConversation():

foreach ( $users_ids as $user_id ) {
  $conv_user = new ConversationUsers();
  $conv_user->conv_id = $conv->id;
  $conv_user->user_id = $user_id;
  try{
    $conv_user->save();
  } catch ( \Exception $ex ) {
    // This is where it must be going wrong!!
  }
}

You could try var_dumping the exception in the catch.

from tbmsg.

woooww your version is old.....

can you tell me the version?

you can use in terminal:

composer show -i | grep tzookb

and paste the output here

from tbmsg.

Old? Iḿ using dev-master in my composer file and tryed doing a composer update but no updates are found. Running composer show -i | grep tzookb returns:
tzookb/tbmsg dev-master f7df3bb users messaging system

Commit f7df3bb is the lateste commit in the master branch here on github.

from tbmsg.

so try to do an

composer update tzookb/tbmsg

because I see the function here
https://github.com/tzookb/tbmsg/blob/master/src/Tzookb/TBMsg/Repositories/EloquentTBMsgRepository.php

and it is different

from tbmsg.

and your commit is the latest.... it is really weird....

from tbmsg.

I already tryed composer update alot of times. I have the latest version for sure.
I'm also looking at: https://github.com/tzookb/tbmsg/blob/master/src/Tzookb/TBMsg/Repositories/EloquentTBMsgRepository.php

and the code there is the same as my local copy!
I'm looking at line 39 to 63. That's the createConversation() function.

public function createConversation( $users_ids ) {
  if ( count($users_ids ) > 1 ) {
    //create new conv
    $conv = new ConversationEloquent();
    $conv->save();
    //get the id of conv, and add foreach user a line in conv_users
    foreach ( $users_ids as $user_id ) {
      $conv_user = new ConversationUsers();
      $conv_user->conv_id = $conv->id;
      $conv_user->user_id = $user_id;
      try{
        $conv_user->save();
      } catch ( \Exception $ex ) {
      }
    }
    $eventData = [
      'usersIds' => $users_ids,
      'convId' => $conv->id
    ];
    return $eventData;
  } else
    throw new NotEnoughUsersInConvException;
}

from tbmsg.

hhhh so ok you are at the latest, I checked it because few comments above you said this

@RaoulDijksman :

After looking at your code I can see there a Try - Catch block in Tzookb/TBMsg/Repositories/EloquentTBMsgRepository.php under the function createConversation():

foreach ( $users_ids as $user_id ) {
  $conv_user = new ConversationUsers();
  $conv_user->conv_id = $conv->id;
  $conv_user->user_id = $user_id;
  try{
    $conv_user->save();
  } catch ( \Exception $ex ) {
    // This is where it must be going wrong!!
  }
}

and this code doesnt exist....

so if it doesnt work yet, we are still in step one....

from tbmsg.

ohhhh sorry found it, I AM STUPID!!

from tbmsg.

please remove the try and catch... it is useless!

and run again check if you get an exception

from tbmsg.

Haha, oke good :P

So what we need to do is throw the exception or dump the $ex there, so we can see whatś going on.

Edit: Oke removing try catch now... 1min

from tbmsg.

AAAA there we go, finaly a error we can work with:

SQLSTATE[42703]: Undefined column: 7 ERROR: column "id" does not exist LINE 1: ...users" ("conv_id", "user_id") values ($1, $2) returning "id" ^ (SQL: insert into "conv_users" ("conv_id", "user_id") values (10, 1) returning "id")

Im going to check my database now.... I did do artisan migrate --package="....." but looks like something went wrong....

from tbmsg.

ahhh I think I know what the problem is, wait a sec

from tbmsg.

My database is update and correct.

from tbmsg.

Looking at the error again, postgres cant find the ID colum in the return. That's the problem.

from tbmsg.

this is because the Model "ConversationUsers"
doesnt have a primary key overide, so it thinks it is "id"

I trying to find ho to overide the key with two columns.

from tbmsg.

This should work in the model:

protected $primaryKey = 'xxxxx';

Do note that the primary key should be unique, both conv_id and user_id aren't. Btw i almost never use a primary key in my pivot tables with out a problem. Why is Eloquent expecting an return?

from tbmsg.

this would work for a single primary key... but this is a pivot table bot columns are a key together.

This is a bug in the architecture of the code, it is not good habit to create a pivot table row directly (as said in some forum post)

until I do a big refactor for using eloquent with its pivot table "attach" method,
I would create the "ConversationUsers" row with raw sql.

Let me try to do a quick fix

from tbmsg.

Exactly, there is no need to make models from pivot table's as the pivot connection should be part of the original model in the first place. Doing that refactor should also reduse the number of database queries the packages had to do.

But using raw SQL here should be good as a hotfix!

Thanks again

from tbmsg.

please do an update and check if it helped....

from tbmsg.

Im getting the following error now: Undefined property: stdClass::$msgId

 $results = $this->tbmRepo->getConversationMessages($conv_id, $user_id, $newToOld);
$conversation = new Conversation();
foreach ( $results as $row )
{
$msg = new Message();
$msg->setId( $row->msgId ); // THIS IS RED
$msg->setContent( $row->content );
$msg->setCreated( $row->created_at );

In /tzookb/tbmsg/src/Tzookb/TBMsg/TBMsg.php

Must be the $msg->setId( $row->msgId );

from tbmsg.

please tell me which file and row number, and of course which function are you using?

I have tests on most of the code and it passes.. so I want to find it

from tbmsg.

I have a test on this area and it passes, can you var_dump the $row

from tbmsg.

Haha yeah sorry, posted that too fast xD

I am getting this befor sending a message, when requesting the show, getConversationMessages

Here is the stack trace:

[2015-01-16 10:41:56] local.ERROR: exception 'ErrorException' with message 'Undefined property: stdClass::$msgId' in /var/www/laravel/2015011301/vendor/tzookb/tbmsg/src/Tzookb/TBMsg/TBMsg.php:165
Stack trace:
#0 /var/www/laravel/2015011301/vendor/tzookb/tbmsg/src/Tzookb/TBMsg/TBMsg.php(165): Illuminate\Exception\Handler->handleError(8, 'Undefined prope...', '/var/www/larav...', 165, Array)
#1 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(214): Tzookb\TBMsg\TBMsg->getConversationMessages(11, 1, false)
#2 /var/www/laravel/2015011301/app/controllers/ConversationsController.php(66): Illuminate\Support\Facades\Facade::__callStatic('getConversation...', Array)
#3 /var/www/laravel/2015011301/app/controllers/ConversationsController.php(66): Tzookb\TBMsg\Facade\TBMsg::getConversationMessages(11, 1, false)
#4 [internal function]: ConversationsController->show('redlion383')
#5 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(231): call_user_func_array(Array, Array)
#6 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(93): Illuminate\Routing\Controller->callAction('show', Array)
#7 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(62): Illuminate\Routing\ControllerDispatcher->call(Object(ConversationsController), Object(Illuminate\Routing\Route), 'show')
#8 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Routing/Router.php(962): Illuminate\Routing\ControllerDispatcher->dispatch(Object(Illuminate\Routing\Route), Object(Illuminate\Http\Request), 'ConversationsCo...', 'show')
#9 [internal function]: Illuminate\Routing\Router->Illuminate\Routing\{closure}('redlion383')
#10 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Routing/Route.php(109): call_user_func_array(Object(Closure), Array)
#11 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Routing/Router.php(1028): Illuminate\Routing\Route->run(Object(Illuminate\Http\Request))
#12 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Routing/Router.php(996): Illuminate\Routing\Router->dispatchToRoute(Object(Illuminate\Http\Request))
#13 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Foundation/Application.php(775): Illuminate\Routing\Router->dispatch(Object(Illuminate\Http\Request))
#14 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Foundation/Application.php(745): Illuminate\Foundation\Application->dispatch(Object(Illuminate\Http\Request))
#15 /var/www/laravel/2015011301/vendor/barryvdh/laravel-debugbar/src/Middleware/Stack.php(34): Illuminate\Foundation\Application->handle(Object(Illuminate\Http\Request), 1, true)
#16 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Session/Middleware.php(72): Barryvdh\Debugbar\Middleware\Stack->handle(Object(Illuminate\Http\Request), 1, true)
#17 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Cookie/Queue.php(47): Illuminate\Session\Middleware->handle(Object(Illuminate\Http\Request), 1, true)
#18 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Cookie/Guard.php(51): Illuminate\Cookie\Queue->handle(Object(Illuminate\Http\Request), 1, true)
#19 /var/www/laravel/2015011301/vendor/stack/builder/src/Stack/StackedHttpKernel.php(23): Illuminate\Cookie\Guard->handle(Object(Illuminate\Http\Request), 1, true)
#20 /var/www/laravel/2015011301/vendor/laravel/framework/src/Illuminate/Foundation/Application.php(641): Stack\StackedHttpKernel->handle(Object(Illuminate\Http\Request))
#21 /var/www/laravel/2015011301/public/index.php(48): Illuminate\Foundation\Application->run()
#22 {main} [] []

Edit: Will try a var_dump in a sec. Will report back

from tbmsg.

now var dump the $row variable to see what you get there.

from tbmsg.

Update: This error only happens when a message is sent.
I will do the var_dump in a sec, sorry. Have to do some stuff at my internship work.

from tbmsg.

hhh ok so post it, I need to go as well, I could check in the evening.

from tbmsg.

The var_dump() returns the following:

object(stdClass)#344 (5) { ["msgid"]=> int(2) ["content"]=> string(4) "test" ["status"]=> int(2) ["created_at"]=> string(19) "2015-01-16 10:49:52" ["userid"]=> int(1) } 

As you can see the colum is named msgid. The $row->msgId in your code is being translated to a colum name with msg_id. Camal case to snake case. So we need to change the colum name or the code.

This isn't the only one, here is a list of all the errors i could found:

Tzookb\TBMsg\TBMsg->getConversationMessages():
- line 166: $row->msgId
- line 169: $row->userId 

Tzookb\TBMsg\TBMsg->getUserConversations():
- line 118: $conv->msgId 
- line 123: $conv->userId

If i were you i would update the colum names instead of updating all the code.

from tbmsg.

I ll check that later... Which columns you mean?
On Jan 16, 2015 1:19 PM, "RaoulDijksman" [email protected] wrote:

The var_dump() returns the following:

object(stdClass)#344 (5) { ["msgid"]=> int(2) ["content"]=> string(4) "test" ["status"]=> int(2) ["created_at"]=> string(19) "2015-01-16 10:49:52" ["userid"]=> int(1) }

As you can see the colum are named msgid. The $row->msgId in your code is
being translated to a colum name with msg_id. Camal case to snake case.
So we need to change the colum name or the code.

This isn't the only one, here is a list of all the errors i could found:

Tzookb\TBMsg\TBMsg->getConversationMessages():- line 166: $row->msgId- line 169: $row->userid Tzookb\TBMsg\TBMsg->getUserConversations():- line 118: $conv->msgId - line 123: $conv->userid

If i were you i would update the colum names instead of updating all the
code.

—
Reply to this email directly or view it on GitHub
#32 (comment).

from tbmsg.

In the var_dump you can see that it has a property called msgid but in your code your calling for a property called msg_id. Same thing with userid. I think you should make it msg_id and user_id

from tbmsg.

After running your SQL query for getConversations() function in Tzookb/TBMsg/Repositories/EloquentTBMsgRepository.php:

SELECT msg.conv_id as conv_id, msg.created_at, msg.id msgId, msg.content, mst.status, mst.self, us.'.$this->usersTableKey.' userId
FROM '.$this->tablePrefix.'messages msg
INNER JOIN (
SELECT MAX(created_at) created_at
FROM '.$this->tablePrefix.'messages
GROUP BY conv_id
) m2 ON msg.created_at = m2.created_at
INNER JOIN '.$this->tablePrefix.'messages_status mst ON msg.id=mst.msg_id
INNER JOIN '.$this->usersTable.' us ON msg.sender_id=us.'.$this->usersTableKey.'
WHERE mst.user_id = ? AND mst.status NOT IN (?, ?)
ORDER BY msg.created_at DESC

Postgres is returning:

 conv_id |     created_at      | msgid | content | status | self | userid 
---------+---------------------+-------+---------+--------+------+--------
      13 | 2015-01-16 11:21:14 |     4 | Ola!    |      2 | t    |      1

As you can see, even though you said to alias msg_id as msgId, Postgres is returning msgid. Same thing with userId.

A google search brought me to the Postgres mailing list. Apartenly this is normal behavior with postgres:

both the identifier names and alias names are folded to lower case. 
Note that adding quotes for aliases will be blessed by PostgreSQL and then
those will be folded to upper case

This means the fix here would be to add qutoes around the alias or change the alias to msg_id and user_id

from tbmsg.

Tnx for the problem solving, I would do it later tonight.
You are welcomed to a pull request and ill add it of course after tests will show green ;)

from tbmsg.

Haha i would like to make a pull request, the only problem is that i`m still at work. I'll make a pull request tonight.

from tbmsg.

Sorry, your last commit didn't fix the problem. The query that is run is still the same for userId.

SELECT msg.conv_id as conv_id, msg.created_at, msg.id "msgId", msg.content, mst.status, mst.self, us.id userId
FROM messages msg
INNER JOIN (
SELECT MAX(created_at) created_at
FROM messages
GROUP BY conv_id
) m2 ON msg.created_at = m2.created_at
INNER JOIN messages_status mst ON msg.id=mst.msg_id
INNER JOIN users us ON msg.sender_id=us.id
WHERE mst.user_id = '1' AND mst.status NOT IN ('0', '3')
ORDER BY msg.created_at DESC

You need to place quotes around userId too.
I would also recomand sticking to the SQL quotes.

from tbmsg.